1

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)
BDolaan
  • 13
  • 3
  • 1
    you have the `"` in the wrong places. For example the begining should be: `"=IFERROR(INDEX('Campaign Report'!Q2:Q" & lSpend & "...` But Also you are mixing A1 and R1C1 references which is not allowed. Change them all to A1. – Scott Craner Mar 03 '21 at 18:30
  • also `ActiveCell.Select` is redundant. The active cell is already selected. – Scott Craner Mar 03 '21 at 18:31
  • `Range(ActiveCell)` should just be `ActiveCell` – Warcupine Mar 03 '21 at 18:31
  • you should also use `ActiveCell.FormulaArray` – Scott Craner Mar 03 '21 at 18:32
  • 1
    One should avoid using activecell and use the actual references: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Scott Craner Mar 03 '21 at 18:33
  • 1
    Your formula is a mish-mash of VBA and worksheet syntax. What does it look like if you enter it by hand on the sheet? Without that it's difficult to help you fix the VBA version. – Tim Williams Mar 03 '21 at 18:46
  • Consider also `SUMIFS`. Also do you need VBA? Just write formulas in Excel UI. – Parfait Mar 03 '21 at 18:47
  • Do you have a typo in there? Should `(G2=)` be `(G=)` ? Unless you're always matching on G2. – Tim Williams Mar 03 '21 at 21:49
  • I am currently using G2 to test if it works for that specific column. Ideally I would have the Column change as the active cell is offset to the next column i.e. it checks H2 when the active cell is in column H – BDolaan Mar 03 '21 at 21:58
  • Maybe it would help to explain exactly what columns are involved and how the formula should change depending on what cell is being populated. There seems to be some missing information here. – Tim Williams Mar 03 '21 at 22:02
  • On the Campaign Sheet, Column A = date F = Campaign Name Q = the value I want to pull. The sheet with the active cell has the campaign names going through row 2 and the dates in column A. Right now the formula accounts for the date which is always in column A (in the same row as the active cell), but I'd like it to also always check the second row (in the same column as the active cell) and use that to match with the names in Column F on the Campaign sheet. After the date and names have been matched, the corresponding value (in column q of Campaign sheet) should be placed in the active cell. – BDolaan Mar 03 '21 at 23:58
  • I have also added the formula I used in Excel UI to the bottom of the post. This formula accomplished exactly what I needed, however I am now trying to make it be more dynamic / able to work across multiple different workbooks that pull from the same data. – BDolaan Mar 04 '21 at 00:03

1 Answers1

1

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks so much for the help. I am a bit confused about what the "" would be in the first replace line. Sorry for the confusion as once again I am very new to VBA. – BDolaan Mar 03 '21 at 19:24
  • "rw" is the row number for the cell `c` – Tim Williams Mar 03 '21 at 19:25
  • You can use `debug.print f` and check the output is what you expect. – Tim Williams Mar 03 '21 at 19:26
  • If you have code to share please edit your question and add it there: it's hard to make sense of it in comments – Tim Williams Mar 03 '21 at 21:07
  • Just updated it in the question. If I understand correctly I would normally be getting a #NA error but due to my IFERROR statement it is just being replaced with a 0. Regardless the code is not pulling the data from Q to the active cell – BDolaan Mar 03 '21 at 21:15