VBA Macro question

mikefromUK

New Member
Joined
Oct 15, 2002
Messages
42
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

New Member
Joined
Oct 15, 2002
Messages
42
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top