VBA Loop Index Match Formula

Question

Hi,

I currently use an index match function to work out ratios for products in column A, based on their criteria in columns B and C.

I have created a Macro typing in this index match function and copied down to product 2 / row 3. However, I want this Macro to loop for whenever I add multiple rows /new products to column A - not just for rows 2 + 3. Not sure where to go from here - I am new to VBA and Macros!

Answer 1

If you move the columns N:Q of sheet 1 to the columns A:D of

Sheet 2 (with the name "Sheet2"), the macro changes as follows:

Sub macro2() 

Dim lr1 As Integer, lr2 As Integer, p As Integer 

lr2 = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, 1).End(xlUp).Row 

With Sheets("Sheet1") 

lr1 = .Cells(.Rows.Count, 1).End(xlUp).Row 

For x = 2 To lr1 

For y = 2 To lr2 

If Sheets("Sheet2").Cells(y, 1) = .Cells(x, 2) And Sheets("Sheet2").Cells(y, 2) = .Cells(x, 3) Then 

For p = 5 To 9 

If Sheets("Sheet2").Cells(y, 3) = .Cells(1, p) Then 

.Cells(x, p) = Sheets("Sheet2").Cells(y, 4) * .Cells(x, 4) 

End If 

Next p 

End If 

Next y, x 

End With 

End Sub

Answer 2

When the name of the sheet with your data is Sheet1, you can use the macro below.

Make sure there are no blank lines in the macro!

Sub macro1() 

Dim lr1 As Integer, lr2 As Integer, p As Integer 

With Sheets("Sheet1") 

lr1 = .Cells(.Rows.Count, 1).End(xlUp).Row 

lr2 = .Cells(.Rows.Count, 14).End(xlUp).Row 

For x = 2 To lr1 

For y = 2 To lr2 

If Application.And(.Cells(y, 14) = .Cells(x, 2), .Cells(y, 15) = .Cells(x, 3)) Then 

For p = 5 To 9 

If .Cells(y, 16) = .Cells(1, p) Then 

.Cells(x, p) = .Cells(y, 17) 

End If 

Next p 

End If 

Next y, x

End With 

End Sub

Answer 3

Don't forget

Application.ScreenUpdating = True

at the end of the macro!

Answer 4

With the data you posted (it took hours to take them over) there is no match whatsoever between the two sheets. Do you really have 71 different sizes?

In column F of sheet "Sizing Data" there are numbers with- and without %. That's not wise.