1

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

Improve this question

3 Answers 3

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Maybe part of the confusion is to which type the information is in Excel. Some of our part numbers are all numbers, others are numbers and a hyphen, and others are letters and numbers. The format of the fields in Excel is classed as text currently. That would make it a string, correct?
@DanaSkyware - No matter the format this code should work. Since a string variable will turn anything it collects into string type.
Using your code, it returned a Row of '0' when looking for the part number 1504915-0013. I've pasted a link to a screenshot of the current spreadsheet. dropbox.com/s/zzbvvgnsqxpa0wj/spreadsheet_sample.png?dl=0
@DanaSkyware - Is the part number hand-typed into Input Box? Or scanned? If scanned there may be extra spaces or something in the value that is throw it off. Try adding sPart = Trim(sPart). If that still doesn't work, do a debug.print sPart and copy and paste the EXACT line in the immediate window (blank space and all).
at the moment I am hand typing it, I was going to test with the scanner once I have it working at the base level. There's a lot more I'm doing with this than this one part. I need the row to be able to add a scanned quantity to the Scanned Qty column for the correct part number, and the Scan Qty number needs to stay red until it is equal to the Quantity Needed.
|
1

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).

Improve this answer

Comments

0 
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

Improve this answer

2 Comments

Your code gives Run Time error 13 Type Mismatch on the Set FindRow line.
I'm not sure why it throws that error but I did figure out when it is throwing it. It seems that error shows up when the currently selected cell is not part of the search range that is defined. I've edited the code above to handle that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.