CODEWITHSUNDEEP
excelvba
JSP
JSP

Reputation: 145

How to find the address of a cell found by LOOKUP?

I use the following formula to get the last non-zero value in a column:

ActiveSheet.Range("A5").Formula = "= LOOKUP(2,1/(H:H>1),H:H)"

It works nicely. But I would like to get the address of that cell. I have tried several lines of code found here and there and most boil down to something like this:

`Dim rb as Range
rb ="LOOKUP(2,1/(H:H>1),H:H)"
MsgBox(rb.Address)
MsgBox(rb.Row)
MsgBox(rb.Column)`

It goes without saying that it does not work. How can one find the address of the cell that LOOKUP returns? Thank you very much in advance.

Upvotes: 0

Views: 568

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

If you want to stick with VBA, and use your original concept, you can use your current Formula, and afterwards use Find function to look from the end to find the row.

Code

Option Explicit

Sub GetAddressofLookup()

Dim FindRng As Range

ActiveSheet.Range("A5").Formula = "= LOOKUP(2,1/(H:H>1),H:H)"

Set FindRng = ActiveSheet.Columns("H").Find(what:=ActiveSheet.Range("A5").Value, Lookat:=xlPart, LookIn:=xlFormulas, _
                        searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False)

MsgBox FindRng.Address


End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

This worksheet formula will give the address of the last value in column H that is greater than zero:

="H"&LOOKUP(2,1/(H:H>0),ROW(H:H))

enter image description here

In VBA:

Sub dural()
    MsgBox "H" & Evaluate("LOOKUP(2,1/(H:H>0),ROW(H:H))")
End Sub

Upvotes: 1

Related Questions