Hi,
I'd be very grateful to anyone who could explain the problem with the following Excel VBA macro. I created it with Macro Record and then edited it. The macro should search through three sheets looking for a string (e.g. 'author'). Once it finds a match the macro should stop and ask if it should continue.
Problem is, even when the string is present in the sheets the Find code in the macro fails to find the string. I've checked for spelling errors etc by using the Excel search function (ctrl F) to find the string. Every time ctrl F finds the string (e.g. 'author') that the macro search code fails to find.
A futher question I have is that sometimes I want to search for a whole match (LookAt:=xlWhole) and other times for a partial match (I Googled the web and looked at several Excel VBA books, but failed to find the equivalent to xlWhole for a partial search - anyone know?). Can I create a string variable which I could substitute for xlWhole in the LookAt parameter e.g. Lookat:=PartialMatchOnly?
My thanks in advance for your help and suggestions.
Here is my macro:
Sub SearchForTagsx()
'SearchForTags Macro
' Searches for a tag in each of the tag sheets in sequence
Dim Rng As Range
Dim TagToBeFound As String
Dim InputIfToContinue As String
Sheets("Tags Insert").Select
Range("F6").Select
TagToBeFound = ActiveCell.Value
Sheets("Tags I").Select
Set Rng = ActiveSheet.Range("A1:M56").Find(What:=TagToBeFound, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Rng Is Nothing Then
InputIfToContinue = InputBox("Continue searching? 'Y' or 'N'")
If InputIfToContinue = "n" Then
GoTo EndSubNow
End If
End If
Sheets("Tags II").Select
Set Rng = ActiveSheet.Range("A1:M56").Find(What:=TagToBeFound, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Rng Is Nothing Then
InputIfToContinue = InputBox("Continue searching? 'Y' or 'N'")
If InputIfToContinue = "n" Then
GoTo EndSubNow
End If
End If
Sheets("Tags III").Select
Set Rng = ActiveSheet.Range("A1:M56").Find(What:=TagToBeFound, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not Rng Is Nothing Then
InputIfToContinue = InputBox("Continue searching? 'Y' or 'N'")
If InputIfToContinue = "n" Then
GoTo EndSubNow
End If
End If
Sheets("Tags Insert").Select
EndSubNow:
End Sub