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

0
votes

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.

1
excelvbaexcel-formula
Instead of *LastRow* use " & LastRow & " - Teamothy
I only used LastRow in this post to emphasize on LastRow. Placing & LastRow & doesn't work. - HoekPeter
Yes for that you will have to find the last row as shown Here - Siddharth Rout
Thanks, but i know how LastRow works, but placing LastRow in the formula like that does not work when i test it - HoekPeter
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

1 Answers

0
votes

"=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),"""")"`