Search code examples
excelvbauserform

Is there a way to search text and numbers in a userform vlookup?

I have created a user form with Vlookups to tell me alternative pantone colours to use.

This is working as expecting when I enter a numerical value I get alternatives (including text alternatives e.g 303 suggests 301 & black).

However, I get an error when I try to enter a colour in the initial search that includes text (e.g WG6).

If i remove "CLng" then it no longer works for my numerical values.

Anyone know what I have missed please?

Private Sub CommandButton1_Click()
Unload Me

UserForm1.Show
End Sub

Private Sub Pantonetb_afterupdate()

With Me

.rp1 = Application.WorksheetFunction.VLookup(CLng(Me.Pantonetb), Sheets("live Pantones").Range("A:F"), 5, False)
.rp2 = Application.WorksheetFunction.VLookup(CLng(Me.Pantonetb), Sheets("Live Pantones").Range("A:F"), 6, False)

End With
End Sub

Appreciate any help.

Solution

  • Using my comment of using FIND and OFFSET and this data:
    enter image description here

    Public Sub Test()

    'Pass the value to search for and column to return.
    Debug.Print MyLookUp(303, 1)    'Returns $B$3_Result
    Debug.Print MyLookUp(303, 3)    'Returns $D$3_Result
    Debug.Print MyLookUp("WG6", 2)  'Returns $C$5_Result
    
End Sub

Public Function MyLookUp(MyLookUpValue As String, ReturnColumn As Long) As String

    Dim MyResult As Range
    With Sheet1.Range("A3:A7")
    
        'Search for the value.
        Set MyResult = .Find(What:=MyLookUpValue, _
                             After:=.Cells(1, 1), _
                             LookIn:=xlValues, _
                             LookAt:=xlWhole, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlNext)
        'Short version of the Find line:
        'Set MyResult = .Find(MyLookUpValue, .Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext)
        
        If Not MyResult Is Nothing Then 'Anything found?
            MyLookUp = MyResult.Offset(, ReturnColumn) 'Offset from the found value by the specified number of columns.
        End If
    End With

End Function