CODEWITHSUNDEEP
excelvba
BDolaan
BDolaan

Reputation: 13

Writing VBA using INDEX / MATCH w/ dynamic variables & Multiple sheets

I am very new to VBA and currently working on a project. The project I am working on consists of two sheets. One sheet consists of a table where the data will be placed and the other consists of all the data. I am trying to write an Index/Match that uses column Q from the Campaign Report sheet as the output, and matches the dates in the first column of the active cell with the dates in column A of the campaign report sheet. I also match the campaign names on the table (Second row in the active cell column) with column f in the campaign report sheet. Once the match is found the corresponding value in column Q of the Campaign report sheet should be output to the active cell

I have gotten this to work before, but am now trying to make it dynamic as the campaign report sheet is going to constantly have new data added to it and I can't figure out how to make the INDEX/MATCH formula work using varibales.

Once again I am very new so I know that the formula is not written properly but I am stuck as to how to fix it. Below is the code:

Dim ws As Worksheet, lastRow As Long, c As Range
Dim f As String

Set ws = Worksheets("Campaign Report")

lastRow = Application.Max(ws.Range("Q100000").End(xlUp).Row, _
                       ws.Range("A100000").End(xlUp).Row, _
                       ws.Range("F100000").End(xlUp).Row)

Set c = ActiveCell

f = "=IFERROR(INDEX(<addrQ>, MATCH(1,(A<rw>=<addrA>)*(G2=<addrF>),0)),0)"
f = Replace(f, "<rw>", c.Row)
f = Replace(f, "<addrA>", "'" & ws.Name & "'!A$2:A$" & lastRow)
f = Replace(f, "<addrF>", "'" & ws.Name & "'!F$2:F$" & lastRow)
f = Replace(f, "<addrQ>", "'" & ws.Name & "'!Q$2:Q$" & lastRow)

c.Formula = f

Set c = c.Offset(0, 1)

Here is an example of the formula array I used in Excel UI to accomplish what I needed:

=IFERROR(INDEX('Campaign Report'!$Q$2:$Q$166,MATCH(1,(A145='Campaign Report'!$A$2:$A$166)*($G$2='Campaign Report'!$F$2:$F$166),0)),0)

Here is the formula that is produced from the vba code. Active cell is G146 and the active cell is filled with a 0 instead of the corresponding value in column Q of the Campaign Report sheet.

=IFERROR(INDEX('Campaign Report'!Q$2:Q$163, MATCH(1,(A146=@'Campaign Report'!A$2:A$163)*(G2=@'Campaign Report'!F$2:F$163),0)),0)

Upvotes: 1

Views: 530

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Try something like this:

Dim ws As Worksheet, lastRow As Long, c As Range
Dim f As String

Set ws = Worksheets("Campaign Report")

'no point in having 3 different "last row"s - just take the max value
lastRow = Application.Max(ws.Range("Q100000").End(xlUp).Row, _
                           ws.Range("A100000").End(xlUp).Row, _
                           ws.Range("F100000").End(xlUp).Row)

Set c = ActiveCell 'or some specific range

'Use replaceable tokens, instead of concatenating strings,
'   which can/does get messy and difficult to debug
f = "=IFERROR(INDEX(<addrQ>, MATCH(1,(A<rw>=<addrA>)*(B<rw>=<addrF>),0)),0)"
f = Replace(f, "<rw>", c.Row)
f = Replace(f, "<addrA>", "'" & ws.Name & "'!A$2:A$" & lastRow)
f = Replace(f, "<addrF>", "'" & ws.Name & "'!F$2:F$" & lastRow)
f = Replace(f, "<addrQ>", "'" & ws.Name & "'!Q$2:Q$" & lastRow)

c.Formula = f
'or c.Value = ws.Evaluate(f)

Set c = c.Offset(0, 1) 'no need to select

Upvotes: 1

Related Questions