vba search for word and go to cell

[ste33uka]

Hi could someone give me a vba that would bring up a pop up box where i would type, it would then search column "A"
for the word typed from the current row +1 downwards, once word is found , it would go to cell and show popup box that word is found,
lastly once it had searched column "a" to last row it would bring up pop up box saying " complete"
Thank

 

[Georgiboy]

You could try the below:

Sub test()
    Dim ItemCnt As Long, fCnt As Long
    Dim fRng As Range, rRng As Range
    Dim sStr As String
    
    sStr = InputBox("Search string")
    Set rRng = Range("A:A") ' column to search
    Set fRng = Range("A1")
    
    ItemCnt = Application.WorksheetFunction.CountIf(rRng, sStr)
    
    If Not ItemCnt = 0 Then
        Do
            Set fRng = rRng.Find(sStr, fRng)
            fCnt = fCnt + 1
            fRng.Select
            MsgBox "Instance: " & fCnt & " found"
        Loop While Not fCnt >= ItemCnt
    End If
    MsgBox "Complete"
End Sub

 

[ste33uka]

example the word "red" is found in cell a22, a100 , a1000 and a2000

When vba is ran , i would type in popup box "red", it would go to cell a22, and pop up box would say "found".
i press ok ,
When vba is ran again , i would type in popup box "red", it would go to cell a100,and pop up box would say "found".
i press ok
When vba is ran again , i would type in popup box "red",it would go to cell a1000,and pop up box would say "found".
i press ok
When vba is ran again , i would type in popup box "red",it would go to cell a2000,and pop up box would say "found".
i press ok
When vba is ran again , i would type in popup box "red, it would search to last row and no more instances of "red" is found it woud display message
"complete"
basically the same of how CTRL +F works , but only searches column "a" and has pop up messages

 

[ste33uka]

no sorry not correct Georgiboy
when i press ok on 1st popup box , i want to manully run macro again as i might need to make edit

 

[Georgiboy]

Maybe try it as below then:

Dim fRng As Range
Sub test()
    Dim fCnt As Long
    Dim rRng As Range
    Dim sStr As String
    
    sStr = InputBox("Searh string")
    Set rRng = Range("A:A") ' column to search
    If fRng Is Nothing Then
        Set fRng = Range("A1")
    End If
    
    If Application.CountIf(Range(fRng.Offset(1), Cells(Rows.Count, 1)), sStr) > 0 Then
        Set fRng = rRng.Find(sStr, fRng)
        fRng.Select
        MsgBox "Found"
    Else
        Set fRng = Nothing
        MsgBox "Complete"
    End If
End Sub

Note the first dim is outside of the sub, this is intentional.

 

[ste33uka]

Thanks alot Georgiboy thats perfect

 

[Legacy 143009]

Note the first dim is outside of the sub, this is intentional.

Oh I didn't know that global values are stored in the memory. I was using them just to make a variable accessible by different functions

 

[Georgiboy]

You're welcome, glad we could help, thanks for the feedback.

Just as a note, if you choose NOT to run the code until the 'Complete' message then fRng will store its value and upset future runs of the code.
The below code will reset fRng back to Nothing should you need to:

Sub Clear_fRng()
    Set fRng = Nothing
End Sub

 

[Georgiboy]

Oh I didn't know that global values are stored in the memory. I was using them just to make a variable accessible by different functions

They are but if the file is closed they reset, they can be handy though when used outside of the sub.