Doing a Left VLOOKUP using VBA code?

[Darth_Sullivan]

I know it's possible to do a left lookup, I am currently using this formula on the worksheet:

=VLOOKUP($B4,CHOOSE({1,2},Sheet1!$C$2:$C$65536,Sheet1!$B$2:$B$65536),2,FALSE)

Unfortunately, I cannot use this. This spreadsheet has several userforms to populate my spreadsheet with data. When using this on the spreadsheet, anytime I add data using the userforms, excel freezes up.

My assumption is that i can attach this formula to a button click on a user form, therefore only returning the value when I want it. My problem comes when trying to translate what this code does to vba.

This is what I thought would give me what I wanted:

Private Sub CommandButton1_Click()

Dim top1 As Long
Dim top2 As Long
Dim top3 As Long
Dim top4 As Long
Dim top5 As Long

Dim name1 As String
Dim name2 As String
Dim name3 As String
Dim name4 As String
Dim name5 As String

Dim PTSrng As Range
Dim NAMErng As Range

Set PTSrng = Worksheets("Sheet1").Range("$C$2:$C$65536")
Set NAMErng = Worksheets("Sheet1").Range("$B$2:$B$65536")

top1 = WorksheetFunction.Large(PTSrng, 1)
top2 = WorksheetFunction.Large(PTSrng, 2)
top3 = WorksheetFunction.Large(PTSrng, 3)
top4 = WorksheetFunction.Large(PTSrng, 4)
top5 = WorksheetFunction.Large(PTSrng, 5)

name1 = WorksheetFunction.VLookup(top1, Choose(1, 2, PTSrng, NAMErng), 2, False)
name2 = WorksheetFunction.VLookup(top2, Choose(1, 2, PTSrng, NAMErng), 2, False)
name3 = WorksheetFunction.VLookup(top3, Choose(1, 2, PTSrng, NAMErng), 2, False)
name4 = WorksheetFunction.VLookup(top4, Choose(1, 2, PTSrng, NAMErng), 2, False)
name5 = WorksheetFunction.VLookup(top5, Choose(1, 2, PTSrng, NAMErng), 2, False)

Label1 = name1
Label2 = top1

Label3 = name2
Label4 = top2

Label5 = name3
Label6 = top3

Label7 = name4
Label8 = top4

Label9 = name5
Label10 = top5


End Sub

When I click, I'm get:

Run-time error '1004':
Unable to get the VLookup property of the WorksheetFunction class

I am very new to learning vba programming, which is why I have everything spaced out and spelled out with as much detail. That is just for me to try to understand what I am doing.

Thank you for your time.

tl;dr version: How to I translate the above formula to work in vba code?

 

[Jonmo1]

The more traditional solution to VLOOKUP's Zoolander disability is to use Index/Match.

Try
=INDEX(Sheet1!$B$2:$B$65536,MATCH(B4,Sheet1!$C$2:$C$65536,0))

 

[Darth_Sullivan]

The more traditional solution to VLOOKUP's Zoolander disability is to use Index/Match.

Try
=INDEX(Sheet1!$B$2:$B$65536,MATCH(B4,Sheet1!$C$2:$C$65536,0))

Thank you for the quick response.
How would I translate this for use in vba so I can attach it to a button click in a user form?

 

[Darth_Sullivan]

Thank you for the quick response.
How would I translate this for use in vba so I can attach it to a button click in a user form?

Got it! I was able to translate it myself using the same method I used to translate my first formula.

Thanks a ton Jonmo1!

 

[Jonmo1]

In VBA I would do it like this

Dim x As Variant, lr As Long

With Sheets("Sheet1")
    lr = .Cells(Rows.Count,"C").End(xlup).Row
    x = Application.Match(Range("B4").Value, .Range("C1:C" & lr),0)
    If Not IsError(x) Then
        Label1 = .Cells(x, "B").Value
    End If
End With

 

[Jonmo1]

Glad you got it sorted, thanks for the feedback.