Excel lookup for a value

[majuranus]

Hello everybody,

I am trying to make a function that will pop up an input window where the user should give a string. Then i want to activate (select) the row(s) that the string was found. The search takes place only in col A

this is some code i found, and mess with it. Problem is that it only gets the first result. Any help would be gratefull

Dim sMessage As String
Dim sTitle As String
Dim sResponse As String
sMessage = "Enter string"
sTitle = "Search using name column"
FindString = InputBox(sMessage, sTitle)
If Trim(FindString) <> "" Then
        With Sheets("sheet1").Range("A:A")
            Set Rng = Cells.Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, False
            Else
                MsgBox "no data found. sorry"
            End If
        End With
    End If

 

[Marcelo Branco]

Hi,

Welcome to the board

The macro below was adapted from
http://www.rondebruin.nl/delete.htm
for select, instead of delete, the rows that contain a specific value entered by the user

Sub Union_Example()
    'Mr Excel Forum - Thread 554036
    'Adapted from [URL]http://www.rondebruin.nl/delete.htm[/URL]
    Dim Firstrow As Long, Lastrow As Long, Lrow As Long
    Dim CalcMode As Long, ViewMode As Long
    Dim sMessage As String, sTitle As String, FindString As String
    Dim rng As Range
 
    sMessage = "Enter string"
    sTitle = "Search using name column"
    FindString = InputBox(sMessage, sTitle)
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
 
    'Using Sheet1 but you can replace this with
    'Sheets("MySheet")if you want
    With Sheets("Sheet1")
        'We select the sheet so we can change the window view
        .Select
 
        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
 
        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False
 
        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
 
        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the A column in this example
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If .Value = FindString Then
                        'This will select each row with the Value = FindString
                        'in Column A, case sensitive.
                        If rng Is Nothing Then
                            Set rng = .Cells
                        Else
                            Set rng = Application.Union(rng, .Cells)
                        End If
                    End If
                End If
            End With
        Next Lrow
    End With
 
    If Not rng Is Nothing Then
        'Select all rows in one time
        rng.Select
    Else
        'No data found
        MsgBox "no data found. sorry"
    End If
 
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

HTH

M.