Search code examples
excelvbastring-conversion

Obtaining and Comparing Integers in a String


I'm trying to take a string input with numbers, ex. (1, 5, 7, 10, 16, 53, 2) and analyze it to see if any one value is >=some integer value.

I know that there's a sort of parseInt function but I don't think this would work for the format and content of the string, as it contains multiple numbers separated by commas.
I know that there's also a function (InStr) to find a certain value in a string, but this doesn't fit my needs as it would require writing cases from 1 to 1000+.

Would transforming the string into an integer array work? Is it a complicated process for VBA?

pseudo code

Dim element As Range
Dim OrgListcolE As Range

For each element in OrgListcolE
    If there exists a value in the element.Value >=16 Then
        ...
    ElseIf there exists a value in element.Value >=51
        ...
    ElseIf there exists a value in element.Value >=251
        ...
    ElseIf there exists a value in element.Value >=1001
        ...
    End If

Solution

  • Loop and check:

    Sub topcheck()
        Dim s As String, limt As Long
        s = "1, 5, 7, 10, 16, 53, 2"
        arr = Split(s, ", ")
        limt = 50
        For Each a In arr
            If CLng(a) > limt Then
                MsgBox "a value in the string exceeds " & limt
                Exit Sub
            End If
        Next a
        MsgBox "no value in the array exceeds " & limt
    End Sub