Excel VBA Find macro fails to find a string even when the string is present in sheet!

[Divadog]

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