CODEWITHSUNDEEP
excelfindvba
Dustin Wehri
Dustin Wehri

Reputation: 1

Excel VBA .find command not working for me

I have an Excel 2013 sheet that has lots of tabs. Each tab holds license keys for a different piece of software that our company has. The License is then tied to the Computer it is installed on. I am writing a macro that lists all the tabs that a a search string is found on. If i leave the inputbox empty, it will list all the tabs. But if i type in something to search for it does nothing but show the end of macro message.

Sub TempMacro()
    Dim ws As Worksheet
    SearchListRow = 3
    Dim rng As Range
    Dim FindSting As String

    FindString = InputBox("Enter a Search value")

    For Each ws In ThisWorkbook.Sheets

        'Set Search Function
        Set rng = Cells.Find(What:=FindString, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

        If Not rng Is Nothing Then
            'Record tab name where string was found
            Sheets("LISTS").Range("O" & SearchListRow) = ws.Name
            'incriment row
            SearchListRow = SearchListRow + 1
        Else
            'MsgBox ("Nothing found in " & wb.Name)
        End If
    Next ws
    MsgBox ("End Macro")
End Sub

Upvotes: 0

Views: 8192

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

That is because your cells object is not fully qualified. What you are doing is searching the activesheet only. You need to add Ws before Cells so that it searches in those sheets

Try this

Set rng = Ws.Cells.Find(What:=FindString......

also LookAt:=xlWhole does an exact match. For partial matches, you will have to use xlPart

Upvotes: 1

Related Questions