CODEWITHSUNDEEP
arraysvbaexcellookup
Mcnanny
Mcnanny

Reputation: 27

Find last cell value in array

I'm looking for a vba code to find the last value in an array in a column. It's also possible with the function LOOKUP(C1; A:B) but I would like to do it with VBA. I've already tried to use the function: x = Application.Worksheetfunction.Lookup(Range("C1"), Range("A:B")) But it didn't work. The data looks something like this:

So for 1 it has to return 12, for 2 -> 30, for 3 -> 40 and for 4 -> 55.

Upvotes: 1

Views: 541

Answers (2)

user3598756
user3598756

Reputation: 29421

edited after OP's further request

I guess you may be after this:

With Range("C1")
    If IsError(Application.Match(.Value, Range("A:A"), 0)) Then
        MsgBox "Sorry, no match for " & .Value & " in column A"
    Else
        Range("H7").Value = Application.VLookup(.Value, Range("A:B"), 2)
    End If
End With

Upvotes: 2

CLR
CLR

Reputation: 12289

Using your example, I think you want:

Application.Worksheetfunction.VLookup(Range("C1"), Range("A:B"),2)

Upvotes: 0

Related Questions