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

 

[Andrew1234]

Hi all,

I have news. So i have manually changed all cells that contain barcodes to be formatted for text. and also changed the .value to .text in the code above. Im very pleased to say that i have tested and it seems to be working correctly and now finding barcodes with number and barcodes with numbers and letters.

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

this is the code i have changed. So now when the barcode is being added to the product data sheet it is being added as a text not a number. which then allows the match function to find the data in the product data base.
Thanks to both @dmt32 and @Joe4 for all of your help this was very much appreciated.

The only minor issue i will have now is the error message that is visible when a number is stored as text. but i think there is maybe a way to ignore these errors ?

But even if there isn't my main object has been achieved. Many thanks to all!

 

[MARK858]

but i think there is maybe a way to ignore these errors ?

select from the first cell with the error to the last, then on the first cell click the Ignore error option

 

[Joe4]

Excellent!
Glad it appears to be working now.