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

To add:

I have managed to do below - but the data is not appearing.

Secondly even with this VBA Code the data continues past row 3 when I thought I included the last row formula to stop.

Answer 4

Dear Joe Sanders1, 

Thanks for posting in Microsoft Community. 

We would love to help you with your query about VBA code, however, our team focuses on general queries, for example, installation and activation issues of Office 365 products. The situation you mentioned is related to VBA code, you can refer to this article: Office VBA support and feedback | Microsoft Docs to go to Stack Overflow by using the VBA tag, along with any other relevant tags as there are also many experienced engineers and experts in the forums there. 

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites, or any support related to technology.

At the same time, we will also keep this thread open, so other Community members and Experts can also share their suggestions and inputs. 

Thank you for your cooperation and understanding! 

Sincerely,

Sean | Microsoft Community Moderator

Answer 5

Hi - many thanks for this - this is populating the percentages - however, I need to multiply the percentages by the total quantity in column D to get a number and not a percentage - like what the index formula is doing in the screenshot. Can this be built into your Macro?