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
– HoekPeter, Commented Jan 27, 2020 at 10:28
Yes for that you will have to find the last row as shown Here — Siddharth Rout
– Siddharth Rout, Commented Jan 27, 2020 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
– HoekPeter, Commented Jan 27, 2020 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
– Siddharth Rout, Commented Jan 27, 2020 at 10:33

Siddharth Rout · Accepted Answer · 2020-01-27 10:36:23Z

0

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

answered Jan 27, 2020 at 10:36

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related