lookup to return last value in vba

[p4nny]

Hi,

I have the following formula which returns the last value in a lookup range.


LOOKUP(2,1/(case=D2),status)

I would now to replicate this in vba.. D2 would become a textbox called 'tbcasenum'

Any guidance would be appreciated

 

[Fishboy]

Hi,

I have the following formula which returns the last value in a lookup range.


LOOKUP(2,1/(case=D2),status)

I would now to replicate this in vba.. D2 would become a textbox called 'tbcasenum'

Any guidance would be appreciated

Hi p4nny, is it not just...

Sub LookupLastValue()
    Worksheets("Sheet1").Range("A5").Formula = "=LOOKUP(2,1/(case=D2),status)"
End Sub

...where the red bold can be changed to suit your sheet name and blue bold can be changed to suit where you want the formula to output to?

 

[p4nny]

Cheers for ther reply..

Sorry I wasn't clear enough. The vba will sit behind a userform so "tbcasenum" is the lookup value and not D2.

Many thanks for your help