Using IN() in VBA 3

[grnzbra]

I have the following code:
If dgt IN("0","1","2","3","4","5","6","7","8","9") Then
pnm = pnm & dgt
End If

I am looping through phone numbers, social security numbers and similar character strings that may have varying formats and trying to produce a sting of just the number.

This gives me a "Compile Error: Syntax Error" message

I've also tried dgt >= 0 and dgt <= 9. That tells me I can't do numeric comparisons with characters

I've also tried between 0 and 9 and I get a message saying that it was looking for a Then or a GoTo and the word "between" is highlighted.

How can I determine if the character is between 0 and 9?

Or, better yet, is there a VBA function that strips off formatting?

 

[Blorf]

Where are you getting the Digit from? Mid function?

Either way, >="0" and <="9" is what you would use on text charachters.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[BSman]

This should work:

If dgt between "0" and "9" THEN
pnm = pnm & dgt
End if


I'm assuming that both dgt and pnm are text variables.

Bob S.

 

[grnzbra]

Of course! Thanks guys. I've always had problems with the obvious. I would have spent hours trying to figure this one out.

 

[PHV]

I wonder VBA don't choke on this:
If dgt between "0" and "9" THEN

Between is an SQL operator not a VBA one.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[grnzbra]

It did choke on the between but it ran the >= "0" and <= "9" ok. Sorry, I only just got a chance to actually check it. My boss's boss just rudely interrupted me with some secretarial work.

Thanks for the help guys.

 

[SkipVought]

Hi,

Modify to suite your needs...

Function MakeList(rng As Range)
    Dim r As Range
    Const TK = "'"
    Const CM = ","
    For Each r In rng
        With r
            MakeList = MakeList & TK & .Value & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

Then use in where clause...

....
sSQL = sSQL & "Where [Field] In (" & MakeList([MyRange]) & ") "

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}