WorksheetFunction.Match not working for numbers

[Andrew1234]

Hi i have a piece of code that is not working correctly for numbers. it will work perfectly when the cell value has at least one letter in it, but if the cell value is only numbers it will not work.


I have a very limited understanding of VBA so any help at all is very much appreciated!

here is the code that am currently using.

Dim TargetRow As Integer

TargetRow = Application.WorksheetFunction.Match(Sheets("Engine").Range("B9").Value, Sheets("Product Data").Range("dyn_Product_Barcode"), 0)

Sheets("Engine").Range("B5") = TargetRow

Engine B9 show the last selected barcode. Engine B5 is meant to show the row that the selected barcode is in.

If there is no issue with the code i think think that my issue could be something to do with how my cell is formatted as when it is stored as a number the cell value is something like this ... 5.01102E+12
whereas when it is stored as text it will just show the barcode as it is ...5011020103539

Also... When i select a barcode that has only numbers i can see in my engine sheet that the value of B9 will be stored as a text even though it is a number.
Everytime i change the format of engine B9 to general but it always autochanges back to text when i select a new product from the list box. Could this be my issue?
Cell B9 is generated from a listbox within a userform. I have a search userform that i search for products on and then click the product i require then i can click an edit button. (the edit button is the one that has the .Match code in it)

There are many areas that could cause this issue so i am very grateful to anyone who takes the time to help me.

Many thanks in advance!
Andrew

 

[dmt32]

Hi,
see if this update to your code will resolve your issue

Sub Andrew1234()
    Dim TargetRow   As Variant, Search As Variant
    Dim wsEngine    As Worksheet
    Dim rngSearch   As Range
    
    Set wsEngine = ThisWorkbook.Worksheets("Engine")
    Set rngSearch = ThisWorkbook.Worksheets("Product Data").Range("dyn_Product_Barcode")
    
    Search = wsEngine.Range("B9").Value
    If IsNumeric(Search) Then Search = CLng(Search)
    
    TargetRow = Application.Match(Search, rngSearch, 0)
    If Not IsError(TargetRow) Then wsEngine.Range("B5") = TargetRow
    
End Sub

Dave

 

[Andrew1234]

Hi Dave, many thanks for your reply.

I have copied your code and replaced my code with your and i recieve an error seen in the image below.

Here is the code in full. hopefully this gives you more understanding of my issue.

Option Explicit

Private Sub SearchUfEditButton_Click()

Sheets("Engine").Range("B4").Value = "EDIT"
Sheets("Engine").Range("B9") = Search_UF.ListBox1


'If Sheets("Engine").Range("B9") = "" Then
'Exit Sub
'End If
 Dim TargetRow   As Variant, Search As Variant
    Dim wsEngine    As Worksheet
    Dim rngSearch   As Range
    
    Set wsEngine = ThisWorkbook.Worksheets("Engine")
    Set rngSearch = ThisWorkbook.Worksheets("Product Data").Range("dyn_Product_Barcode")
    
    Search = wsEngine.Range("B9").Value
    If IsNumeric(Search) Then Search = CLng(Search)
    
    TargetRow = Application.Match(Search, rngSearch, 0)
    If Not IsError(TargetRow) Then wsEngine.Range("B5") = TargetRow



'Dim TargetRow As Integer

'TargetRow = Application.WorksheetFunction.Match(Sheets("Engine").Range("B9").Value, Sheets("Product Data").Range("dyn_Product_Barcode"), 0)
'Sheets("Engine").Range("B5") = TargetRow



'''BEGIN RECIEVING DATA FROM DATABASE'''
AddProduct_UF.Txt_barcode = Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Value 'barcode to UF
AddProduct_UF.Txt_prodtitle = Sheets("Product Data").Range("Data_start").Offset(TargetRow, 2).Value 'product title to UF
AddProduct_UF.Txt_description = Sheets("Product Data").Range("Data_start").Offset(TargetRow, 3).Value 'product description to UF
AddProduct_UF.Combo_category = Sheets("Product Data").Range("Data_start").Offset(TargetRow, 4).Value 'category selected to UF
AddProduct_UF.Combo_location = Sheets("Product Data").Range("Data_start").Offset(TargetRow, 5).Value 'location in store to UF
AddProduct_UF.Txt_supplier = Sheets("Product Data").Range("Data_start").Offset(TargetRow, 6).Value 'supplier information UF
AddProduct_UF.Txt_cost = Sheets("Product Data").Range("Data_start").Offset(TargetRow, 7).Value 'unit cost of item UF
'''END RECIEVING DATA FROM DATABASE'''

AddProduct_UF.Show


End Sub

many thanks,
Andrew

 

[Joe4]

I wonder if you have the same issue as I mentioned here: Vba: Application.VLookup doesn't work with numeric values (error 2042)

Jusy like VLOOKUP, the data types of the data you are matching on MUST be EXACTLY the same.
If you are trying to match numbers to "numbers entered as text", it will not work. You need to standardize the data so you are comparing like data types.

 

[dmt32]

I have copied your code and replaced my code with your and i recieve an error seen in the image below.

which line is the shows the error?

Dave

 

[Andrew1234]

@dmt32 see image below.

thanks,
Andrew

 

[Andrew1234]

I wonder if you have the same issue as I mentioned here: Vba: Application.VLookup doesn't work with numeric values (error 2042)

Jusy like VLOOKUP, the data types of the data you are matching on MUST be EXACTLY the same.
If you are trying to match numbers to "numbers entered as text", it will not work. You need to standardize the data so you are comparing like data types.

Hi joe,

Yes i do think that my issue is something similar. How to i standardise the data?

I am not an expert in excel by any means, but i tried selecting relevant cells and changing to format to general and then trying it as a text and also a number but I didn't succeed with this. so i am not sure

 

[dmt32]

See if coercing to a longlong data type resolves the issue

If IsNumeric(Search) Then Search = CLngLng(Search)

Dave

 

[Joe4]

Hi joe,

Yes i do think that my issue is something similar. How to i standardise the data?

I am not an expert in excel by any means, but i tried selecting relevant cells and changing to format to general and then trying it as a text and also a number but I didn't succeed with this. so i am not sure

Do you have any value that have leading zeroes which must be maintained?
Can you post a small sampling of the data you are matching on (just the data from the columns being matched)?
Be sure to include the different types (you said some have letters and some do not).

 

[Andrew1234]

See if coercing to a longlong data type resolves the issue

If IsNumeric(Search) Then Search = CLngLng(Search)

Dave

Thank you but unfortunately i have received another error.
but it is a different error than previously so the change has done something .
Any other suggestions?


Regards
Andrew