VBA to pop up input box and search and select user defined string or number

[Sandman1985]

Hi All,

Hope you're all well, I've been trying to get a macro button to make an input box pop up which allows me to enter in a text string or number to find and select on a very large sheet of data.
I know I can use Ctrl+F, but some of the people who need to use this will have their minds blown with that kind of change. SO I want to just put a large button which prompts them to enter what they are looking for.
I have the Input Box working, but can't get the find and select working.

Dim FindS As String
Dim Found As Boolean

FindS = InputBox("Enter the job number/name you want to search")

If FindS = "" Then
Exit Sub
End If

my code after this to find doesn't work.
have tried searching and appending my code with other code but failed.
Any help greatly appreciated.

Cheers,

Sandman

 

[abdelfattah]

Hi
your code doesn't search for anything , just if the inputbox is empty will exit from procdeure

 

[Sandman1985]

Hi
your code doesn't search for anything , just if the inputbox is empty will exit from procdeure

Hi,

Yes I only pasted the section that was working. I've been messing with it a bit.

Sub Search2()
Dim fnd As Range
Dim Ws As Worksheet

fnd = InputBox("Enter the job you want to search", "Job Number or Name")

For Each Ws In Worksheets
Set fnd = Ws.UsedRange.Find(InputBox, , xlValues, xlPart, , , False, , False)
If Not fnd Is Nothing Then
Ws.Activate
fnd.Select
Exit Sub
End If

End Sub

Still no good. Falling over in the range.

 

[abdelfattah]

try this

Sub Search2()
Dim fnd As Variant
Dim Ws As Worksheet

fnd = InputBox("Enter the job you want to search", "Job Number or Name")

For Each Ws In Worksheets
Set fnd = Ws.UsedRange.Find(fnd, , xlValues, xlPart, , , False, , False)
If Not fnd Is Nothing Then
Ws.Activate
fnd.Select
Exit Sub
End If
Next
End Sub

 

[dmt32]

Hi,
see if this update to your code does what you want

Sub Search()
    Dim fnd         As String
    Dim FoundCell   As Range
    Dim Ws          As Worksheet
    
    Do
        fnd = InputBox("Enter the job you want To search", "Job Number Or Name")
        'cancel pressed
        If StrPtr(fnd) = 0 Then Exit Sub
    Loop Until Len(fnd) > 0
    
    For Each Ws In Worksheets
        Set FoundCell = Ws.UsedRange.Find(fnd, , xlValues, xlPart, , , False, , False)
        If Not FoundCell Is Nothing Then
            Ws.Activate
            FoundCell.Select
            Exit Sub
        End If
    Next Ws
    
End Sub

Dave

 

[Sandman1985]

Hi,
see if this update to your code does what you want

Sub Search()
    Dim fnd         As String
    Dim FoundCell   As Range
    Dim Ws          As Worksheet
   
    Do
        fnd = InputBox("Enter the job you want To search", "Job Number Or Name")
        'cancel pressed
        If StrPtr(fnd) = 0 Then Exit Sub
    Loop Until Len(fnd) > 0
   
    For Each Ws In Worksheets
        Set FoundCell = Ws.UsedRange.Find(fnd, , xlValues, xlPart, , , False, , False)
        If Not FoundCell Is Nothing Then
            Ws.Activate
            FoundCell.Select
            Exit Sub
        End If
    Next Ws
   
End Sub

Dave

That worked a treat. Thank you dmt32!

 

[Sandman1985]

try this

Sub Search2()
Dim fnd As Variant
Dim Ws As Worksheet

fnd = InputBox("Enter the job you want to search", "Job Number or Name")

For Each Ws In Worksheets
Set fnd = Ws.UsedRange.Find(fnd, , xlValues, xlPart, , , False, , False)
If Not fnd Is Nothing Then
Ws.Activate
fnd.Select
Exit Sub
End If
Next
End Sub

That almost worked. The one below got it. Thanks for your help

 

[dmt32]

Glad we were able to resolve & appreciate feedback

You may like to add a msgbox to inform users if search value not found

Sub Search()
    Dim fnd         As Variant
    Dim FoundCell   As Range
    Dim Ws          As Worksheet
   
    Do
        fnd = InputBox("Enter the job you want To search", "Job Number Or Name")
        'cancel pressed
        If StrPtr(fnd) = 0 Then Exit Sub
    Loop Until Len(fnd) > 0
   
    For Each Ws In Worksheets
        Set FoundCell = Ws.UsedRange.Find(fnd, , xlValues, xlPart, , , False, , False)
        If Not FoundCell Is Nothing Then
            Ws.Activate
            FoundCell.Select
            Exit Sub
        End If
    Next Ws
    MsgBox fnd & Chr(10) & "Record Not Found", 48, "Not Found"
End Sub

Dave