Test for formula in cell
 

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

Test for formula in cell
 

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

"Fred Holmes" wrote:

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

Test for formula in cell
 

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

"Fred Holmes" wrote:

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

Test for formula in cell
 

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.
--
Gary''s Student - gsnu200856


"Fred Holmes" wrote:

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

Test for formula in cell
 

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" wrote in message
...
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

Test for formula in cell
 

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

Fred Holmes