Variable

[Matt]

Having a problem substituting a variable into code. I'm trying to replace the word COMPLETE with the variable t which also happens to be the word COMPLETE. x is defined as boolean and when I use the variable, I keep getting a false value when it should in fact be true. Any help appreciated.

x = Evaluate("=NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))")

x = Evaluate("=NOT(ISERROR(SEARCH(" & t & "," & usedcell.Address & ",1)))")

thanks

Matt

[Jay Petrulis]

On 2002-05-20 07:49, Matt wrote:
Having a problem substituting a variable into code. I'm trying to replace the word COMPLETE with the variable t which also happens to be the word COMPLETE. x is defined as boolean and when I use the variable, I keep getting a false value when it should in fact be true. Any help appreciated.

x = Evaluate("=NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))")

x = Evaluate("=NOT(ISERROR(SEARCH(" & t & "," & usedcell.Address & ",1)))")

thanks

Matt

Hi Matt,

Try something like the following:

Sub test()
Dim x As Boolean, t
t = "COMPLETE"

On Error Resume Next
x = Not IsEmpty(WorksheetFunction.Search(t, ActiveCell, 1))
On Error GoTo 0

MsgBox x
End Sub

For testing purposes, I used ActiveCell, rather than your variable usedcell. Change it in your code.

However, please note that I did *not* use the .Address property, as this is searching for a string, not a range reference. In essence, you were searching for "COMPLETE" in "$A$1" rather than in cell A1.

To be more precise, replace your UsedCell.Address with UsedCell.Value or just UsedCell in the above code.

Let us know if this works for you.

Bye,
Jay

[Matt]

Thanks Jay