Hi All,

The Table I have created is as follows.

Paint Name Colour Boxes
Paint 1 White 5 Available
Paint 1 Green 3 Available
Paint 2 White Out of Stock
Paint 2 Green 10 Available

I have entered the preferred paint as Paint 1 in textbox1. I have two command buttons named White & Green. Now I want to check what is the input in textbox 1 and based on that the data should display when I click white or green button.

Below is the code I have written. But the problem is its checking only the textbox1 input but not both textbox1 and colour. I tried to use match function to filter but getting an error stating "Application or Object Defined Error". Can someone please guide how to solve this or suggestions are welcome if any other method to achieve this rather than using VLookUp.


Sub CommandButton1_Click

    Dim foundCLR As Range
    Dim searchCLR As String    
    searchCLR = "White"
    Set foundCLR = Sheets("Sheet1").Columns(2).Find(searchCLR, LookIn:=xlValues, Lookat:=xlWhole)
    If Not foundCLR Is Nothing Then
        Dim getPNT As String
        getPNT= Sheets("Sheet1").Range("A2") 'Get the Paint Input
        Set myrange = Sheet5.Range("A:E")
        Label1.Caption = Application.WorksheetFunction.VLookup(inc1, Application.WorksheetFunction.match("White", myrange, 0),4, False) 
        or
        Label1.Caption = Application.WorksheetFunction.VLookup(inc1, match("White", myrange, 0),`4, False) 
        
    End If

End Sub
Regards,
Dheepak