Test for formula in cell

[Fred Holmes]

Excel 2000

How do I get the following formula:

If ActiveCell.Offset(0, -4).Formula = " *" Then

to test if the formula/value (hard coded) in the cell is of the form

" ####"

(two spaces followed by four of any digits)?

Two spaces followed by anything would be good enough at present. The
above code produces false on " 1234" when I think it ought to produce
true. Is "*" usable as a wild card?

TIA

Fred Holmes

 

[Trevor Williams]

Hi Fred

Yes, * can be used as a wildcard but you need to change the '=' to 'Like'

If ActiveCell.Offset(0, -4).Formula Like " *" Then

HTH

Trevor Williams

 

[Trevor Williams]

Hi Fred

Yes, * can be used as a wildcard. You need to change the '=' to 'Like'.
If ActiveCell.Offset(0, -4).Formula Like " *" Then

HTH
Trevor Williams

 

[Gary''s Student]

You can either explicitly test the value:

Sub DataTester()
Dim s As String
s = ActiveCell.Offset(0, -4).Value
l = Len(s)
st = Left(s, 2)
en = Right(s, 4)
If l = 6 And st = " " And IsNumeric(en) Then
MsgBox ("value is good")
Else
MsgBox ("value is not good")
End If
End Sub


or use Like and a pattern match.

 

[Patrick Molloy]

as already mentioned, the asterisk is the wildcard for any number of
characters.

if you want specifically to check a space followed by four characters use
LIKE " ????"
or
for space and four digits
LIKE " ####"

HELP on LIKE gives you all these

 

[Fred Holmes]

Thanks to all for your suggestions. Both methods work well.

Fred Holmes