Issues with Input Box

[Chris1025]

I have an input box that is having trouble with preceding zeros. My data source will often have preceding zeros, but more times than not, it won't. See the image for example. The white is how it appears in the spreadsheet, while the black is the full number. The code recognizes them as the same for some reason. How can i prevent this, and have it recognize each as individual numbers because they truly are?

 

[dreid1011]

Excel has a limit to how many digits can be stored for a number, and looks like it is cutting it off after 5541. You would probably be better off storing the values as text to retain the exact value.

 

[Chris1025]

Excel has a limit to how many digits can be stored for a number, and looks like it is cutting it off after 5541. You would probably be better off storing the values as text to retain the exact value.

after checking into that, appears it's 15 digits, and anything after that returns as zero. the format in the data source is already text, and has the full number, how can i have the input box search for both text/numbers? or just text i guess? text would cover both, right?

 

[Joe4]

For numbers, Excel can only store up to 15 significant digits. Everything after that would be zeroed out.

It would be most helpful if you posted your VBA code, some sample data, and expected results.

See these tips for posting your VBA code: How to Post Your VBA Code

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Chris1025]

For numbers, Excel can only store up to 15 significant digits. Everything after that would be zeroed out.

It would be most helpful if you posted your VBA code, some sample data, and expected results.

See these tips for posting your VBA code: How to Post Your VBA Code

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

the input box works great, except for the times where i have twenty digit numbers. it recognizes them as duplicates because the first 16 digits ( i know now excel stops at 15 ) may be the same.

Sub Scan_Barcode()
   Dim BarCode     As Variant
   Dim rngScan     As Range, FoundBar   As Range
   Dim BarCount    As Long
   Set rngScan = Range("B1:BQ4000")
   Do
       BarCode = InputBox("Scan Barcode", "Scan")
       If Len(BarCode) > 0 Then
           If IsNumeric(BarCode) Then BarCode = Val(BarCode)
           BarCount = Application.CountIf(rngScan, BarCode)
           If BarCount = 1 Then
               Set FoundBar = rngScan.Find(BarCode, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
               If Not FoundBar Is Nothing Then FoundBar.Select
               If Not FoundBar Is Nothing Then FoundBar.Interior.Color = rgbYellow
           ElseIf BarCount > 1 Then
               MsgBox "Barcode: " & BarCode & Chr(10) & "There are " & BarCount & " times this Barcode appears. Check for duplicates!", 48, "Duplicates"
           Else
               MsgBox "Barcode: " & BarCode & Chr(10) & "No matching barcode found!", 64, "No Matches"
           End If
       End If
   ''cancel pressed
   Loop Until StrPtr(BarCode) = 0
End Sub

 

[Joe4]

What if you declare "BarCode" as String instead of Variant, and get rid of this line, which is converting some of the values to numbers:

If IsNumeric(BarCode) Then BarCode = Val(BarCode)

 

[Chris1025]

What if you declare "BarCode" as String instead of Variant, and get rid of this line, which is converting some of the values to numbers:

If IsNumeric(BarCode) Then BarCode = Val(BarCode)

it's recognizing this as the same barcode as "00108188420255417048" Because the first 15 numbers are the same.

 

[Joe4]

Oh wow, that is crazy! It looks like despite all that, Excel still converts them to numbers when doing the comparison.
I instead tried using COUNTIF in Conditional Formatting, and it did the same thing there too!
If there are any letters anywhere in the string, it works fine. But if it consists of all numbers and is greater than 15 digits, it is problematic.
Not sure how to get around that, short of adding some text to the front of all your entries.