VBA - Using SearchRange to Find Variable Value

Question

I'm working on an Excel Spreadsheet that pulls a pick list from the SQL Database and populates the sheet. It then prompts the user to Scan a Part number. I am trying to locate the part number in Column A, and return the row for the part number.

The part number begins as a Variant type, but thinking that type was the problem, I converted it to string by setting its value to another variable.

Finally, I found this snippet of code (I've tried many and none have worked so far), and it works when you specify a number (123456789012) as in the code below. It does not work if you replace that number with the variable sPart nor scanPart.

I need it to work to find the row of the variable sPart (String) or scanPart (Variant). What am I doing wrong?

Function ReturnRowNumber()

Dim ws As Excel.Worksheet
Set ws = ActiveSheet

Dim scanPart As Variant
Dim sPart As String
Dim FoundRow As String

scanPart = InputBox("Scan the first part number", "Part Number")  'Get Part Number

sPart = scanPart

MsgBox sPart

Dim SearchRange As Range
Dim FindRow As Range
Set SearchRange = Range("A1", Range("A65536").End(xlUp))
Set FindRow = SearchRange.Find(123456789012#, LookIn:=xlValues, lookat:=xlWhole)
MsgBox FindRow.Row

End Function

Thanks in advance for any help!

Dana

Answer 1

The code as written in your post will NEVER work, as the syntax is not correct, especially in the .Find statement. Perhaps that issue was throwing you in the wrong direction, thinking it was a data type issue.

Please see this code. Tested on both string and numerical values.

Option Explicit

Sub Test()

    Dim sPart As String
    sPart = InputBox("Scan the first part number", "Part Number")  'Get Part Number

    MsgBox ReturnRowNumber(sPart)

End Sub


Function ReturnRowNumber(sPart As String) As Long

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1") 'be more explicit than 'ActiveSheet

    Dim SearchRange As Range
    Dim FindRow As Range
    Set SearchRange = ws.Range("A1", ws.Range("A65536").End(xlUp))
    Set FindRow = SearchRange.Find(sPart, LookIn:=xlValues, lookat:=xlWhole)

    If Not FindRow Is Nothing Then ReturnRowNumber = FindRow.Row

End Function

Answer 2

From your code and the description of your problem it sounds like what is happening is you are defining a variable as Variant, but that Varian is really a String (thus, never use Variant unless you have a very specific reason to).

Something like:

Dim Foo as Variant
Dim Bar as Long

Foo = "123"
Bar = "123"

Is basically like saying:

Dim Foo as String
Dim Bar as Long

Foo = "123"
Bar = CLng("123")

The reason why this is important is because:

12345 <> "12345"

They are different values. One is a Long representation, the other is a String representation.

The solution to your problem? It depends on how your initial values are stored. If you are using String representations of your barcodes then you want to declare your barcode as a String, and use that String to search. It seems though that you are actually storing whole numbers, and if that is the case you will want to declare your barcode as a Long.

If, by some curse, you are storing Barcodes as Strings and as whole numbers, well you will either need to choose one or the other, or you will need to use a more robust find method (I recommend dictionaries).

Answer 3

Function ReturnRowNumber()

Dim ws As Excel.Worksheet
Set ws = ActiveSheet

Dim scanPart As Variant
Dim sPart As String
Dim FoundRow As String
Dim xlCell as Range 
scanPart = InputBox("Scan the first part number", "Part Number")  'Get Part Number

sPart = scanPart    

Dim SearchRange As Range
Dim FindRow As Range
Set SearchRange = Range(Range("A1"), Range("A1").End(xlDown))
Set xlCell = ActiveCell
Application.ScreenUpdating = False
SearchRange.Select
Set FindRow = SearchRange.Find(What:=sPart, After:=ActiveCell, SearchOrder:=xlByRows)
xlCell.Select
Application.ScreenUpdating = True
MsgBox FindRow.Row

End Function

It looks like your find function was not formatted properly. The code above works perfectly for me