Vlookup not working when used in userform

[ijhoeq]

Hello,

I am trying to use VLOOKUP in my userform but I keep getting the error "Run-time '1004': Unable to get the VLookup property of the WorksheetFunction class". This error occurs when I enter 'Case 0' in the code below. Can someone help me figure out why this won't work? Thanks!

Select Case ChargeType

Case 0
Worksheets("Liquid Control Sheet").Activate
Range("B8").Value = Range("B4").Value + 14.7 - Application.WorksheetFunction.VLookup(Range("B7"), Range("A32:B55"), 2, True)

Case 1
Worksheets("Other worksheet").Activate

Case 2
Worksheets("Something Else").Activate

End Select

 

[Special-K99]

I dont think you can put Application.WorksheetFunction within a Case.

I took out the Worksheets and range lines and ran them in a normal subroutine and they worked.
As soon as I added the Case it failed with the same error you're getting.

 

[steve the fish]

Id have thought its more likely the vlookup is producing an error. You can use a vlookup within a case statement.

 

[Special-K99]

Agreed but I didnt get an error when I tested with a value not present.
Even use FALSE at the end of the VLOOKUP.

 

[Special-K99]

Thats weird, working now (??)

Even when the VLOOKUP fails ?

 

[Norie]

Try this.

    Select Case ChargeType
        Case 0
            With Worksheets("Liquid Control Sheet")
                Res = Application.VLookup(.Range("B7").Value, .Range("A32:B55"), 2, True)
                If Not IsError(Res) Then
                    .Range("B8").Value = .Range("B4").Value + 14.7 - Res
                Else
                    MsgBox .Range("B7").Value & " not found in A32:A55 on Liquid Control Sheet"
                End If
            End With

        Case 1
            Worksheets("Other worksheet").Activate

        Case 2
            Worksheets("Something Else").Activate

    End Select