Finding a number in a string in excel VBA

Question

I am trying to find a number in a string given via textbox in excel vba and display an error saying it cant continue and after that it must stop from loading the codes below.

fname is Full Name given by the user

If InStr(fname, "0") Then
    MsgBox ("Number Found In Your Name. Please Correct That!!")

 Dim i(0 To 9) As String
    i(0) = "0"
    i(1) = "1"
    i(2) = "2"
    i(3) = "3"
    i(4) = "4"
    i(5) = "5"
    i(6) = "6"
    i(7) = "7"
    i(8) = "8"
    i(9) = "9"


If InStr(fname, "0") Then
    MsgBox ("Number Found In Your Name. Please Correct That!!")

End If

It is supposed to search in the text given by the user and display an error if it contains a number(list of numbers)

Scott Craner · Accepted Answer · 2019-07-17 13:20:58Z

2

No loop needed, it can be done with one line:

If Fname like "*[0-9]*" Then MsgBox ("Number Found In Your Name. Please Correct That!!")

answered Jul 17, 2019 at 13:20

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aman · Accepted Answer · 2019-07-17 12:24:20Z

1

InStr function is not boolean, it returns integer value. It will give you nth number of the string(fname), if your searching expression(for example "0") is inside string(fname). Otherwise, it returns zero. So, you can try your code like this:

For i=0 to 9
    If InStr(fname, i)>0 Then
        MsgBox ("Number Found In Your Name. Please Correct That!!")
    End If
Next i

answered Jul 17, 2019 at 12:24

Aman

3342 gold badges5 silver badges14 bronze badges

2 Comments

xcx Over a year ago

Can it stop loading the codes below it? That's what I need now.

Aman Over a year ago

I tried doing this, and it runs code inside If everytime. it does not have anything to check.

DistillerWithComputer · Accepted Answer · 2019-07-17 12:30:25Z

1

Maybe a simple answer, but Cstr() changes integers into strings.

For i = 0 To 9
    If InStr(fname, CStr(i)) Then
        MsgBox ("Number Found In Your Name. Please Correct That!!")
        Exit For
    End If
Next

answered Jul 17, 2019 at 12:30

DistillerWithComputer

114 bronze badges

3 Comments

DistillerWithComputer Over a year ago

I'm not sure what you mean. But you can replace the code you posted with the one in the answers.

xcx Over a year ago

after it displays the error message, it shouldn't run the lines of code below it.

DistillerWithComputer Over a year ago

I think I understand. There is more code below this part. If you change Exit For into Exit Sub it should work.

Gary's Student · Accepted Answer · 2019-07-17 12:20:49Z

0

Regex is the best way, but a loop can also work:

Sub dural()
    Dim s As String, i As Long
    s = "james"
    For i = 0 To 9
        If InStr(1, s, CStr(i)) > 0 Then
            MsgBox ("Number Found In Your Name. Please Correct That!!")
            Exit Sub
        End If
    Next i
End Sub

answered Jul 17, 2019 at 12:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

JvdV · Accepted Answer · 2019-07-17 12:35:34Z

0

You could try implement `EVALUATE'

If Evaluate("COUNT(FIND(ROW(1:10)-1,""" & fname & """))") > 0 then
    Msgbox "Number Found In Your Name. Please Correct That!!"
End if

answered Jul 17, 2019 at 12:35

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Collectives™ on Stack Overflow

Finding a number in a string in excel VBA

5 Answers 5

Comments

2 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related