better way than vlookup

depcdivr

Active Member
Joined
Jan 21, 2008
Messages
325
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)

VBA Code:
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.

VBA Code:
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,835
Office Version
  1. 2019
Platform
  1. Windows
Hi,
try using the Range.Find method & see if this will do what you want

VBA Code:
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
 
Solution

depcdivr

Active Member
Joined
Jan 21, 2008
Messages
325
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,835
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,143,909
Messages
5,721,456
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top