Evaluate rng for numeric characters

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Evaluate rng for numeric characters

Post by VegasNath »

Please help with my IF statement below..... I need to evaluate each of the first 10 characters of each 'rng' to ensure that each of the first 10 characters are numeric?

Code: Select all

    Set dstrg = dstws2.Range("C2:C" & dstlr)
    
    For Each rng In dstrg
        If rng NOT = "?????" Then
            rng.Offset(0, 1) = "'10"
        End If
    Next rng
:wales: Nathan :uk:
There's no place like home.....

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Evaluate rng for numeric characters

Post by Jan Karel Pieterse »

If IsNumeric(Left(rng.Value, 10)) Then
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Evaluate rng for numeric characters

Post by VegasNath »

Thanks. I'm close! I only need to evaluate each rng if rng.Offset(0, 1) is not already '10. This is including the '10 items which I need to ignore?

Code: Select all

    Set dstrg = dstws2.Range("C2:C" & dstlr)
    
    For Each rng In dstrg
            If Not rng.Offset(0, 1) = "'10" And Not IsNumeric(Left(rng.Value, 10)) Then
                rng.Offset(0, 1) = "'10"
                rng.Offset(0, 1).Interior.ColorIndex = 3
            End If
    Next rng
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Evaluate rng for numeric characters

Post by HansV »

The apostrophe ' is not considered to be part of the cell value; it indicates to Excel that the value following it is to be treated as text.
So in the If ... Then part, you should not include the apostrophe in the string:

If Not rng.Offset(0, 1) = "10" And ...
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Evaluate rng for numeric characters

Post by VegasNath »

Thanks, I would never have found that.
:wales: Nathan :uk:
There's no place like home.....

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Evaluate rng for numeric characters

Post by Jan Karel Pieterse »

But what if the cell actually contains 10 and not '10 ?
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Evaluate rng for numeric characters

Post by Jan Karel Pieterse »

You could test for:
If rng.Offset(,1).PrefixCharacter & rng.Offset(,1).Value = "'10" Then
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com