VBA - Identifying alpha characters in CSV to import into arrays

[JakeB8]

I'm editing VBA code that was created by someone else several years ago. The end goal is to import data from a CSV file into multiple arrays using multiple VBA subs. The main driver for what to import is in column A of the CSV, which is labeled as "ID." There is one sub that searches the ID values for anything beginning with "L" or "H." That part works fine. There is another sub that searches the ID values for anything else, but if the values aren't strictly numerical then they aren't being recognized. We'd like to be able to have alpha-numeric values here, but right now we can't. Generally the alpha characters we need to recognize (other than L or H) would be at the end of the numeric string, but the numeric string isn't always the same length (ex. 1A, 11A, 111A). How can we guarantee the code would recognize the the alpha-numeric strings that don't start with L or H?

ID	PART​	LENGTH	result​
1A	A12B20-6	90	--> Is not recognized.
2	A12B20-6	1755	--> Recognized and sent to Array 1.
3	A12B20-6	90
4	A12B20-0	115
5	A12B20-0	1730
6	A12B20-0	115
L1	192-C2524	1590	--> Recognized and sent to Array 2.
H1	A71A46-6	1050	--> Recognized and sent to Array 3.

 

[alexos]

Could you not just see whether the last character is numeric or not and then do something (or not) as needed?
Some examples...

Sub Test()

    Dim mystring As String
    
    mystring = "111"
    
    ' Use IsNumeric to check whether the whole string is a number or not.
    If IsNumeric(mystring) = True Then
        MsgBox "Just numbers here: " & mystring, vbOKOnly
        ' Do your thing here
    End If
    
    mystring = "1A1B"
    
    ' Same again. IsNumeric to check whether the whole string is a number or not.
    If IsNumeric(mystring) = False Then
        MsgBox "Numbers and letters here: " & mystring, vbOKOnly
        ' Do your thing here
    End If
    
    mystring = "111B"
    
    ' Now just look at the right hand character and do something based on that.
    If IsNumeric(Right(mystring, 1)) = False Then
        MsgBox "ends with a letter: " & mystring, vbOKOnly
        ' Do your thing here
    End If

End Sub

 

[Fuji]

How can we guarantee the code would recognize the the alpha-numeric strings that don't start with L or H?

Use Like operator e.g.

If Your Data Like "[!HL]*" Then