Hi all,
I have a workbook with two tabs (Source and report)
Source contains 3 columns: Country(col-A), Product(col-B) & Business unit(col-C)
In the report I'm trying to use a macro to create an index match to map the Country and Product to return a Business Unit.
(Country = Col A, Product = Col B, Business Unit (to be mapped in Col C)
My aim is for the loop to run in the report tab from Cells(2,3) to Cells(20,3).
I've seen examples of the "evaluate" syntax used, but haven't been able to make it work.
This is what I have so far:
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Set rng1 = Sheets("Source").Range("$A$1:$A$300")
Set rng2 = Sheets("Source").Range("$B$1:$B$300")
Set rng3 = Sheets("Source").Range("$C$1:$C$300")
For i = 2 To 20
Cells(i, 3) = Evaluate("Index(rng3" & "Match(1,(rng1=(Cells(i, 1)*(rng2=(Cells(i,2),0))"
Next
Help!
Bookmarks