better way than vlookup

[depcdivr]

I Have used a vlookup command in the past to search for a value in a range of data.


IF there then I can use the line in order to pull data to a form. If not I return a msgbox with an error (example of what I have used in the past)

Found = Application.WorksheetFunction.VLookup(Me.scanner_add_input.Value, ws_sku.Range("$A$2:$G$9999"), 2, False)
If Found = "" Then
    MsgBox "SKU " & Me.scanner_add_input.Value & " Not Found.  " & Chr(10) & "Try again"

    Exit Sub
End If


Me.item.Value = Application.WorksheetFunction.VLookup(Me.scanner_add_input.Value, ws_sku.Range("$A$2:$G$9999"), 2, False)
Me.clips.Value = Application.WorksheetFunction.VLookup(Me.scanner_add_input.Value, ws_sku.Range("$A$2:$G$9999"), 3, False)
Me.sku.Value = Me.scanner_add_input.Value
Me.color.Value = Application.WorksheetFunction.VLookup(Me.scanner_add_input.Value, ws_sku.Range("$A$2:$G$9999"), 5, False)

I Have a similar application now but I want to enter a barcode number and have it find it in a range of cells and then once found I want to add 1 to a cell two cells to the right.

I tried using the Vlookup function but it keeps coming back with an error... Runtime error '1004': Unable to get the Vlookup property of the WorksheetFunction Class

Here is the code I am trying to use.

Dim ws As Worksheet

Private Sub barcode_Change()

Set ws = Worksheets("Physical Inventory List")

    irow = ws.Cells(Rows.Count, 2).End(xlUp).Row + 2

If Len(Me.Barcode.Value) <> 13 Then Exit Sub


If Len(Me.Barcode.Value) = 13 Then



Found = Application.WorksheetFunction.VLookup(Me.Barcode.Value, ws.Range("$E$2:$G$9999"), 1, False)
If Found = "" Then
    MsgBox "SKU " & Me.Barcode.Value & " Not Found.  " & Chr(10) & "Try again"

    Exit Sub
End If

I am open to other suggestions on how to accomplish this without the Vlookup.

The data will be in column E(not sorted or but unique with blanks) I want to add 1 to the cell two cells to the of where I find the data.

 

[dmt32]

Hi,
try using the Range.Find method & see if this will do what you want

Private Sub barcode_Change()
    Dim Found           As Range
    Dim Search          As String
    Dim ws              As Worksheet
    Dim SearchColumn    As Variant
    
    Search = Me.Barcode.Value
    If Len(Search) <> 13 Then Exit Sub
    
    Set ws = ThisWorkbook.Worksheets("Physical Inventory List")
    
    SearchColumn = 5
    
    Set Found = ws.Columns(SearchColumn).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        
        'found do stuff
        With Found
            .Offset(, 1).Value = 1
            .Offset(, 2).Value = 1
        End With
        
    Else
    
        MsgBox "SKU " & Search & " Not Found.  " & Chr(10) & "Try again", 48, "Not Found"
        
    End If
    
End Sub

Adjust code as required to meet specific project need

Dave

 

[depcdivr]

Thanks. This mostly works much better.

For some reason when the barcode value is not found the msgbox does not show up as having an issue. If I step into the code the message box appears but nothing shows up if I run it automatically.

It brought me closer to a finished project. Thank you again.

 

[dmt32]

It brought me closer to a finished project. Thank you again.

You will need to adjust suggestion as required but pleased it helps

Appreciate feedback

Dave