Using VBA to create a dynamic formula using End(xlUp)

Question

I'm trying to place a formula in a range of cells using VBA. I have written the formula to do this and it works great, however, I would like to the formula to be dynamic. The range of data in which the formula searches is variable.

I want to do a use End(xlUp) to determine the range. The code I have is the following:

Range("P43").FormulaArray = "=IFERROR(INDEX(Input Tigerlijst!$A$2:$A$247,MATCH(1,--(SEARCH(TRANSPOSE(Input Tigerlijst!$A$2:$A$247),O43)>0),0),0),"""")"

What I think is the solution is:

Range("P43").FormulaArray = "=IFERROR(INDEX(Input Tigerlijst!$A$2:*LastRow*,MATCH(1,--(SEARCH(TRANSPOSE(Input Tigerlijst!$A$2:*LastRow*),O43)>0),0),0),"""")"

I've been searching and trying for hours, help would be greatly appreciated.

I only used *LastRow* in this post to emphasize on LastRow. Placing & LastRow & doesn't work. — HoekPeter, Jan 27 '20 at 10:28
Yes for that you will have to find the last row as shown [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — Siddharth Rout, Jan 27 '20 at 10:29
Thanks, but i know how LastRow works, but placing LastRow in the formula like that does not work when i test it — HoekPeter, Jan 27 '20 at 10:31
That is because you dont need just last row. you need the column address as well. For example `$A$2:*LastRow*` should be `$A$2:$A$" & LastRow &` — Siddharth Rout, Jan 27 '20 at 10:33

score 0 · Accepted Answer · answered Jan 27 '20 at 10:36

"=IFERROR(INDEX(Input Tigerlijst!$A$2:$A$247,MATCH(1,--(SEARCH(TRANSPOSE(Input Tigerlijst!$A$2:$A$247),O43)>0),0),0),"""")"

can be written as

"=IFERROR(INDEX(Input Tigerlijst!$A$2:$A$" & "247" & ",MATCH(1,--(SEARCH(TRANSPOSE(Input Tigerlijst!$A$2:$A$" & "247" & "),O43)>0),0),0),"""")"

or for clarity

"=IFERROR(INDEX(Input Tigerlijst!$A$2:$A$" & _
"247" & _
",MATCH(1,--(SEARCH(TRANSPOSE(Input Tigerlijst!$A$2:$A$" & _
"247" & _
"),O43)>0),0),0),"""")"`

which can finally be written as

"=IFERROR(INDEX(Input Tigerlijst!$A$2:$A$" & _
LastRow & _
",MATCH(1,--(SEARCH(TRANSPOSE(Input Tigerlijst!$A$2:$A$" & _
LastRow & _
"),O43)>0),0),0),"""")"`

Using VBA to create a dynamic formula using End(xlUp)

1 Answers1