using isnumeric with vba

Question

I have an sheet with column D where I have an ID in the format of

MG-456789 ; MG-Series ; MG-.

The above are the cases how the ID looks in my column D.

I would prefer to have an code, which works in such a way that, it checks for the number after MG - if there are 6 digits present, then it is valid, else I want it to be printed as invalid in column S.

For eg: if there is an ID like ; MG-Se then I want column S printed as invalid ; or MG- as invalid ; something like MG-456789 then its accepted and don't need to be printed in column S.

I tried to go through net and found Isnumeric can be used to check for the number in the cell. I could visualize for particular number but not a code for generic case like mine.

Can anyone help me how I can proceed with this case? any lead would be helpful.

Should it always start with "MG-" or are there other letters and lengths allowed? — trincot, Aug 06 '17 at 09:32

score 2 · Accepted Answer · answered Aug 06 '17 at 09:58

2

Try this code.

Sub test()
    Dim vDB, vR()
    Dim Ws As Worksheet
    Dim n As Long, i As Long, s As String
    Set Ws = ActiveSheet
    With Ws
        vDB = .Range("d2", .Range("d" & Rows.Count).End(xlUp))
    End With
    n = UBound(vDB, 1)
    ReDim vR(1 To n, 1 To 1)
    For i = 1 To n
        s = Replace(vDB(i, 1), "MG-", "")
        If Len(s) = 6 And IsNumeric(s) Then
        Else
            vR(i, 1) = "false"
        End If
    Next i
    Ws.Range("s2").Resize(n) = vR
End Sub

answered Aug 06 '17 at 09:58

Dy.Lee

7,527
1
12
14

could you please comment the lines what it is doing ? – Jenny Aug 06 '17 at 10:09
Variant array's explanation is [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10717999#10717999) – Dy.Lee Aug 06 '17 at 13:15

score 1 · Answer 2 · answered Aug 06 '17 at 10:13

1

It's easy using Like operator:

If myString Like "MG-[0-9][0-9][0-9][0-9][0-9][0-9]" Then
    MsgBox "Valid ID"
Else
    MsgBox "Invalid ID"
End If

[0-9] stands for any digit, thus, it will match any string starting with MG- and followed by six digits.

answered Aug 06 '17 at 10:13

Michał Turczyn

32,028
14
47
69

score 1 · Answer 3 · answered Aug 06 '17 at 10:28

You could also write it as a function to be called as

=CheckMG1(D2)

and pulled down

Function CheckMG1(s As String) As String
If Len(s) = 9 And Left(s, 3) = "MG-" And IsNumeric(Right(s, 6)) Then
    CheckMG1 = "OK"
Else
    CheckMG1 = "Invalid"
End If
End Function

score 1 · Answer 4 · answered Aug 06 '17 at 17:11

A simpler code for you to try,

Sub MG()
Dim i As Long
For i = 1 To Cells(Rows.Count, "D").End(xlUp).Row
    If IsNumeric(Replace(Cells(i, "D"), "MG-", "")) Then
        Cells(i, "S") = "Valid"
    Else
        Cells(i, "S") = "InValid"
    End If
Next i
End Sub

using isnumeric with vba

4 Answers4