Using Variable Name in Lookup Formula VBA

Question

It may seem to be an easy one as i am novice at VBA. I am trying to fill the filtered blank visible cells with previous non blank hidden cell value in the same column. The Lookup formula is working fine on excel sheet but using it with variable range in VBA is giving Application defined or object defined Error on lookup formula line.

nlr = Cells(Rows.Count, 9).End(xlUp).Row
With Activehseet
  Application.DisplayAlerts = False
  Range("A1:K" & nlr).AutoFilter Field:=2, Criteria1:=""
  Range("A1:K" & nlr).AutoFilter Field:=1, Criteria1:="P * Suite *"
  Range("B2:B" & nlr).SpecialCells(xlCellTypeVisible).ClearContents
  For Each c In Range("B1:B" & nlr).Offset(1, 0).SpecialCells(xlCellTypeVisible)
      n = c.Row - 1
      c.Formula = "=LOOKUP(2,1/($B$2:B&n&<>""),$B$2:B&n&)"

I have already tried it with below too, but it didn't work

    c.Formula = "=LOOKUP(2,1/($B$2:B " & n & "<>""),$B$2:B" & n & ")"

Please help me resolve this

EDIT: I have already tried this approach, but it didn't work either

c.Formula = "=LOOKUP(2,1/($B$2:B " & n & "<>""""),$B$2:B" & n & ")"

The user already deleted his account after asking the question? — Siddharth Rout
– Siddharth Rout, Commented May 25, 2021 at 8:27
I’m voting to close this question because OP has deleted the account after asking the question. We will never know if the solution(s) ever solved OPs problem. It will be a waste of time to spend any time on this. — Siddharth Rout
– Siddharth Rout, Commented May 25, 2021 at 8:29

Martin · Accepted Answer · 2021-05-25 07:44:20Z

0

This is the correct approach for concatenating a string into another string:

c.Formula = "=LOOKUP(2,1/($B$2:B " & n & "<>""),$B$2:B" & n & ")"

However, there is a problem with this part of it:

... & n & "<>""), ...

That will place one double quote into the text. I presume you are expecting it to look like <>"" when parsed, which means the line should look like this:

... & n & "<>""""), ...

Why? Because in a string to put one double quote you need to put a double, double quote: "" = " (in finished string). Therefore a double, double quote becomes: """" = "".

Here is the corrected original code:

c.Formula = "=LOOKUP(2,1/($B$2:B " & n & "<>""""),$B$2:B" & n & ")"

answered May 25, 2021 at 7:44

Martin

16.5k1 gold badge33 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

Using Variable Name in Lookup Formula VBA

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