MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

check for digit & hyphen

  Asked By: Rabiah    Date: Mar 15    Category: MS Office    Views: 1842

I want to check for digit (1,2,3,4,5,6,7,8,9,0) and hyphen (-) in vba.
Right now I am using this statement

For i = Len(ActiveCell.Value) To 1 Step -1
cch = Mid(ActiveCell.Value, i, 1)
If cch = "1" Or cch = "2" Or cch = "3" Or cch = "4" Or cch = "5" Or _
cch = "6" Or cch = "7" Or cch = "8" Or cch = "9" Or cch = "-" _
Or cch = "0" Then

I want to shorten it

So, the cell should contain only combination of these characters.

The above statement checks all chacaracters one by one.
I used backward loop from my previouse statement so didn't changed it right now.



3 Answers Found

Answer #1    Answered By: Ryan Evans     Answered On: Mar 15

You want to validate the cell  contents, doing nothing if valid, but
executing MY_FUNCTION if not?

Sub blah()
valid = True
For i = 1 To Len(ActiveCell.Value)
cch = Mid(ActiveCell.Value, i, 1)
If InStr("1234567890-", cch) = 0 Then valid = False: Exit For
Next i
If Not valid Then MY_FUNCTION
End Sub

This can be easily edited to check  for the presence of only those
characters within the double quote marks.

Answer #2    Answered By: Aamanee Khan     Answered On: Mar 15

I'd use a For..Next and a Select statement.

For ilN = 1 to Len(Activecell.value)

Select case mid(Activecell.value,ilN,1)
Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "-"
' Do something.
Case Else
' Do something else.
End Select

Next ilN

I use this sort of thing all the time for stuff... Eg Stipping control
characters out.

Answer #3    Answered By: Michele Grant     Answered On: Mar 15

ok not sure if this has been answered or not but i think you should be able
to shorten it if you use the instr function


if not instr(cch, "0123456789-") then
end if

should cover it

end aircode

Didn't find what you were looking for? Find more on check for digit & hyphen Or get search suggestion and latest updates.