Substitute in VBA

[Ed in Aus]

Hi,

I am having trouble with this section of code.

If Len(ActiveCell.Value) - Len(WorksheetFunction.Substitute(ActiveCell.Value, ".", "")) <> Len(ActiveCell.Value) Then GoTo skippy

What I want is if the active cell contains a "." then go to skippy, but the code goes to it anyway.

I am trying to get the lenght of the activecell - the length of the active cell with no "."'s in it

Can anyone help

 

[GTO]

Try:

    If Not InStr(1, ActiveCell.Value, ".", vbTextCompare) = 0 Then
        GoTo skippy
    End If

Hope this helps,

Mark

Edit: ACK! Sorry, just read the second half; shame on me. Okay, which are we trying to do, go to skippy if we find a stop/dot in the active cell, or get the active cell's length (stripped of any stops)?

 

[Legacy 68668]

Or

If ActiveCell.Value Like "*.*" Then GoTo skippy

 

[Ed in Aus]

Thanks Guys,
just went with:

If ActiveCell.Value Like "*.*" Then GoTo skippy

little bit shorter and sweeter works like a treat reading my post I was rushing my typing a fair bit