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]

oh dear should have asked if on 64 bit platform
Given that you have mentioned that may have mix of data types in the range then maybe worth seeing is the Range.Find method would be better approach?

Dave

 

[Andrew1234]

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

yes i have values leading with 0.
The value that the match formula is reading are from barcode scans so there is a possibility that there could be values leading with 0.

here is the userform that populates 'Engine'!B9. i select a row from the list box and then when i select the edit button the code that i have shown above will run.

here is one of my concerns... you can see that in the listbox the barcode values are all in number format but in my product data base it formats differently. Could this be part of the problem?
the reason i say part of the problem is because i have also tried adding short barcodes with just numbers such as 001 and 123 but the search function will still not work.

 

[Joe4]

Yes, you can see from your first image that you have all text entries (you can tell because they are all left-justified). But in your second image, you can see that the all numeric ones are being changed to numbers (you can tell because the long numbers are being changed to scientific notation).

The ideal thing would be to make EVERYTHING text (that way, leading zeroes are also maintained). That would mean we would have to change how column B in your second image is being calculated/populated. How is that column being populated?

 

[Andrew1234]

oh dear should have asked if on 64 bit platform
Given that you have mentioned that may have mix of data types in the range then maybe worth seeing is the Range.Find method would be better approach?

Dave

not sure if i understand, im running of a laptop with 64 bit operating system. is this the answer you are looking?
Yes for sure we could try a different method

 

[Andrew1234]

Yes, you can see from your first image that you have all text entries (you can tell because they are all left-justified). But in your second image, you can see that the all numeric ones are being changed to numbers (you can tell because the long numbers are being changed to scientific notation).

The ideal thing would be to make EVERYTHING text (that way, leading zeroes are also maintained). That would mean we would have to change how column B in your second image is being calculated/populated. How is that column being populated?

The product data table is populated though userforms. theres add product button which allows the user to select if they want to create a barcode for a product or if they would like to scan a new product into the system.
Im just thinking the code for adding to the product data base has .Value at the end of it. If this was changed to .text would it work?

this is the code

'''BEGIN DATA INPUT TO PRODUCTS LIST'''
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 0).Formula = "=""(""&RC[1]&"")""" 'barcode formula
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Value = AddProduct_UF.Txt_barcode.Value 'barcode
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 2).Value = AddProduct_UF.Txt_prodtitle.Value 'product title
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 3).Value = AddProduct_UF.Txt_description.Value 'product description
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 4).Value = AddProduct_UF.Combo_category.Value 'category selected
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 5).Value = AddProduct_UF.Combo_location.Value 'location in store
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 6).Value = AddProduct_UF.Txt_supplier.Value 'supplier information
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 7).Value = AddProduct_UF.Txt_cost.Value 'unit cost of item
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 8).Formula = "=SUM((SUMIFS('Scan Here'!C[-4],'Scan Here'!C[-7],RC[-7],'Scan Here'!C[-6],{""Check-in"",""Rental-in""})) - (SUMIFS('Scan Here'!C[-4],'Scan Here'!C[-7],RC[-7],'Scan Here'!C[-6],{""Check-out"",""Rental-out""})))" 'formula used to count stock from Scan Here sheet.
'''END DATA INPUT TO PRODUCTS LIST'''

Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Value = AddProduct_UF.Txt_barcode.Value 'barcode
.text

would that be any use?

 

[Andrew1234]

The product data table is populated though userforms. theres add product button which allows the user to select if they want to create a barcode for a product or if they would like to scan a new product into the system.
Im just thinking the code for adding to the product data base has .Value at the end of it. If this was changed to .text would it work?

this is the code

'''BEGIN DATA INPUT TO PRODUCTS LIST'''
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 0).Formula = "=""(""&RC[1]&"")""" 'barcode formula
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Value = AddProduct_UF.Txt_barcode.Value 'barcode
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 2).Value = AddProduct_UF.Txt_prodtitle.Value 'product title
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 3).Value = AddProduct_UF.Txt_description.Value 'product description
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 4).Value = AddProduct_UF.Combo_category.Value 'category selected
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 5).Value = AddProduct_UF.Combo_location.Value 'location in store
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 6).Value = AddProduct_UF.Txt_supplier.Value 'supplier information
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 7).Value = AddProduct_UF.Txt_cost.Value 'unit cost of item
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 8).Formula = "=SUM((SUMIFS('Scan Here'!C[-4],'Scan Here'!C[-7],RC[-7],'Scan Here'!C[-6],{""Check-in"",""Rental-in""})) - (SUMIFS('Scan Here'!C[-4],'Scan Here'!C[-7],RC[-7],'Scan Here'!C[-6],{""Check-out"",""Rental-out""})))" 'formula used to count stock from Scan Here sheet.
'''END DATA INPUT TO PRODUCTS LIST'''

Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Value = AddProduct_UF.Txt_barcode.Value 'barcode
.text

would that be any use?

Or is this a completely different thing?

 

[Joe4]

The product data table is populated though userforms. theres add product button which allows the user to select if they want to create a barcode for a product or if they would like to scan a new product into the system.
Im just thinking the code for adding to the product data base has .Value at the end of it. If this was changed to .text would it work?

this is the code

'''BEGIN DATA INPUT TO PRODUCTS LIST'''
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 0).Formula = "=""(""&RC[1]&"")""" 'barcode formula
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Value = AddProduct_UF.Txt_barcode.Value 'barcode
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 2).Value = AddProduct_UF.Txt_prodtitle.Value 'product title
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 3).Value = AddProduct_UF.Txt_description.Value 'product description
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 4).Value = AddProduct_UF.Combo_category.Value 'category selected
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 5).Value = AddProduct_UF.Combo_location.Value 'location in store
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 6).Value = AddProduct_UF.Txt_supplier.Value 'supplier information
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 7).Value = AddProduct_UF.Txt_cost.Value 'unit cost of item
Sheets("Product Data").Range("Data_start").Offset(TargetRow, 8).Formula = "=SUM((SUMIFS('Scan Here'!C[-4],'Scan Here'!C[-7],RC[-7],'Scan Here'!C[-6],{""Check-in"",""Rental-in""})) - (SUMIFS('Scan Here'!C[-4],'Scan Here'!C[-7],RC[-7],'Scan Here'!C[-6],{""Check-out"",""Rental-out""})))" 'formula used to count stock from Scan Here sheet.
'''END DATA INPUT TO PRODUCTS LIST'''

Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Value = AddProduct_UF.Txt_barcode.Value 'barcode
.text

would that be any use?

Yeah, try something like:

Sheets("Product Data").Range("Data_start").Offset(TargetRow, 1).Text = AddProduct_UF.Txt_barcode.Text 'barcode

Also, try pre-formatting column B as "Text" BEFORE entering anything in this column (with your code).
You can either do that manually, or add it to your code above this section of code.

 

[Andrew1234]

here is 'Engine'!B9 after i click a random barcode in from the listbox. this shows there is an issue with cell formating.

 

[MARK858]

To change all to numbers...
Try selecting column B, Data tab, Text to Columns, Next, check that none of the options is checked, Next and click Finish
Then in a blank cell put the formula

=ISNUMBER(B9)

and make sure it returns TRUE

To change all to text...
Try selecting column B, Data tab, Text to Columns, Next, check that none of the options is checked, Next, select the Text checkbox and click Finish

Then in a blank cell put the formula

=ISNUMBER(B7)

and make sure it returns False

 

[dmt32]

not sure if i understand, im running of a laptop with 64 bit operating system. is this the answer you are looking?
Yes for sure we could try a different method

Yes but not to worry - if still cannot resolve using Match method then give following a try & see if any help

Private Sub SearchUfEditButton_Click()
    Dim TargetRow   As Variant, Search As Variant
    Dim wsEngine    As Worksheet, wsProduct As Worksheet
    Dim rngSearch   As Range, RecordRow As Range
    
    With ThisWorkbook
        Set wsEngine = .Worksheets("Engine")
        Set wsProduct = .Worksheets("Product Data")
    End With
    
    Set rngSearch = wsProduct.Range("dyn_Product_Barcode")
    
    With wsEngine
        .Range("B4").Value = "EDIT"
        .Range("B9") = Me.ListBox1
        Search = .Range("B9").Value
    End With
    
    If le(Search) = 0 Then Exit Sub
    
    Set TargetRow = rngSearch.Find(CStr(Search), LookIn:=xlValues, lookat:=xlWhole)
    If Not TargetRow Is Nothing Then
        
        '''BEGIN RECIEVING DATA FROM DATABASE'''
        With AddProduct_UF
            .Txt_barcode.Value = TargetRow.Offset(, 1).Value
            .Txt_prodtitle.Value = TargetRow.Offset(, 2).Value
            .Txt_description.Value = TargetRow.Offset(, 3).Value
            .Combo_category.Value = TargetRow.Offset(, 4).Value
            .Combo_location.Value = TargetRow.Offset(, 5).Value
            .Txt_supplier.Value = TargetRow.Offset(, 6).Value
            .Txt_cost.Value = TargetRow.Offset(, 7).Value
            '''END RECIEVING DATA FROM DATABASE'''
            .Show
        End With
        
    Else
        
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If
    
End Sub

Code is untested & just a guess but may give you another approach to consider

Dave