CODEWITHSUNDEEP
vbaexceltype-conversioncomparison
Keesler Water Walker
Keesler Water Walker

Reputation: 29

Compare exact integers greater than "long integer"

I need to find a better VBA data type in Excel 2013 so I can make a more accurate comparison between two positive 10 digit integers. I've found and tried CULng, but this doesn't seem to exist in this version. Also, CLng doesn't have the range necessary for these numbers.

I have a database of phone numbers (10 digits) and need a way to compare two exact numbers. Some of the entries in my DB are strings listing the first and last of a range so I can't just use If A = B Then (do something). I've tried using CSng, but it rounds the numbers off using exponential format (5555559100 and 5555559150 both become 5.555559e+09 ). The two numbers are then seen as equal even though they don't match. Previous to this code, I have stripped the searching TN down to just the digits but it is still a string value.

Private Sub FindTN()

Dim TN As String
Dim begin  As Single
Dim last As Single
Dim RowNo As Long

Sheet1.Range("A1").Value = "5555559100TO5555559125"
Sheet1.Range("A2").Value = "5555559150TO5555559175"
Sheet1.Range("A3").Value = "5555559160"

TN = "5555559160"

For Each entry In Sheet1.Range("A1:A3")
    If Len(entry.Value) = 10 And entry.Value = TN Then
        RowNo = entry.Row
        Debug.Print "RowNo = " & RowNo

    'Find beginning and ending values of a range
    ElseIf Len(entry.Value) > 10 And InStr(11, entry.Value, "TO") = 11 Then
        begin = CSng(Left(entry.Value, 10))
        last = CSng(Right(entry.Value, 10))

        'Search within range
        If CSng(TN) >= begin And CSng(TN) <= last Then
            RowNo = entry.Row
            Debug.Print "RowNo = " & RowNo
        End If
    End If
Next entry

End Sub

It should match on Row 2 and 3 only, but instead results in

RowNo = 1
RowNo = 2
RowNo = 3

Upvotes: 1

Views: 486

Answers (1)

Dy.Lee
Dy.Lee

Reputation: 7567

Fix variable as double like this.

Private Sub FindTN()

Dim TN As String
Dim begin  As Double 'Single
Dim last As Double ' Single
Dim RowNo As Long

Sheet1.Range("A1").Value = "5555559100TO5555559125"
Sheet1.Range("A2").Value = "5555559150TO5555559175"
Sheet1.Range("A3").Value = "5555559160"

TN = "5555559160"

For Each entry In Sheet1.Range("A1:A3")
    If Len(entry.Value) = 10 And entry.Value = TN Then
        RowNo = entry.Row
        Debug.Print "RowNo = " & RowNo

    'Find beginning and ending values of a range
    ElseIf Len(entry.Value) > 10 And InStr(11, entry.Value, "TO") = 11 Then
        begin = CDbl(Left(entry.Value, 10))
        last = CDbl(Right(entry.Value, 10))

        'Search within range
        If CDbl(TN) >= begin And CDbl(TN) <= last Then
            RowNo = entry.Row
            Debug.Print "RowNo = " & RowNo
        End If
    End If
Next entry

End Sub

Upvotes: 1

Related Questions