VBA Macro question

[mikefromUK]

I am clearly missing something here. I want to test if a particular string is present in a particular worksheet. I don't wish to replace it - just test if it is there. Rather than using the Find method which returns a range, I have used the replace method which returns a boolean variable, replacing the found string with the replace string, ie nothing changes. The replace should return "true" if at least one replace occurs, false if it cannot find the strFindString. Problem: the replace action is returning true, regardless of whether or not it finds the text. It even returns true on an empty spreadsheet? Any ideas. Below is a quick program is "mocked" up to show you what I mean.




Option Explicit


Sub test()

Dim strFindString
strFindString = "australia"
Sheet1.Select
If ActiveSheet.Cells.Replace(What:=strFindString, Replacement:=strFindString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False) = True Then
MsgBox ("condition true")
End If

End Sub

 

[Andrew Poulsom]

The Find method returns Nothing if a match is not found. So:

If ActiveSheet.Cells.Find(What:=strFindString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False) Is Nothing Then

 

[Mudface]

This seems to work OK: -
<pre>
Sub test()

Dim strFindString
Dim c As Range

strFindString = "australia"

With ActiveSheet.Cells
Set c = .Find(What:=strFindString, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False)
If Not c Is Nothing Then
MsgBox "Condition True"
Else:
MsgBox "Condition False"
End If
End With

End Sub
</pre>

 

[mikefromUK]

Brilliant lads!

I was trying find (...) = nothing and it would not work. It works now.

If you are ever in Glasgow, I'll buy you a pint or two.

Cheers

MG