Macro search only 1 column of sheet

[ste33uka]

Hi could would anyone have a macro that would bring up and search box
to search for a word in column "s" of a sheet called "data" ?
I have the following code below if someone could adjust it to just search the 1 sheet and 1 column
Thanks

Sub word()

    Dim srchVal As Variant
    Dim ws As Worksheet
    
'   Specify what to search for (hard-code or range reference)
    srchVal = InputBox("What value do you want to look for?")
    
'   Loop through all sheets and check cell c1 for search value
    For Each ws In Worksheets
        If Format(ws.Range("c1")) = Format(srchVal) Then
            ws.Activate
            MsgBox srchVal & " found on sheet " & ws.Name
        End If
    Next ws
    
End Sub

 

[Saurabhj]

Hi,

Use below code:

There is one commented if statement. To make search case-sensitive, use that.

Sub word()

    Dim srchVal As String
    Dim lastRow As Integer, rowno As Integer
    
'   Specify what to search for (hard-code or range reference)
    srchVal = InputBox("What value do you want to look for?")
    
    lastRow = Sheets("data").Cells(Rows.Count, "S").End(xlUp).Row
    
    For rowno = 1 To lastRow
        'for case sensitive search
        'If InStr(1, Sheets("data").Cells(rowno, "S"), srchVal) > 0 Then

        'Not a case sensitive search
        If InStr(1, UCase(Sheets("data").Cells(rowno, "S")), UCase(srchVal)) > 0 Then
            Sheets("data").Cells(rowno, "S").Interior.Color = vbGreen
        End If
    Next
    
End Sub

 

[mohadin]

what about

Sub word()

    Dim srchVal As Variant
    Dim cel As Range
    Dim Flag As Boolean
    Flag = False
    '   Specify what to search for (hard-code or range reference)
    srchVal = InputBox("What value do you want to look for?")
    For Each cel In Sheets("sheet1").[S:S]
        If Format(cel.Value) = Format(srchVal) Then
            MsgBox srchVal & " found in " & cel.Address
            Flag = True
        End If
        If Flag Then Exit Sub
    Next
End Sub

 

[dmt32]

Hi,
you could use Range.Find Method to do what you want

Sub word()
    
    Dim Search      As String
    Dim FoundCell   As Range
    Dim ws          As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Data")
    
    'Specify what to search for
    Do
        Search = InputBox("What Do you want To search for?", "Search")
        'cancel pressed
        If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
    
    If FoundCell Is Nothing Then Set FoundCell = ws.Cells(1, "S")
startsearch:
    Set FoundCell = ws.Columns("S").Find(Search, After:=FoundCell, LookIn:=xlValues, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
        'match found
        If MsgBox("Search:= " & Search & Chr(10) & _
        "Match found On sheet " & ws.Name & " Cell " & FoundCell.Address & Chr(10) & Chr(10) & _
        "Continue Search?", 36, "Search") = vbYes Then GoTo startsearch
    Else
        'no match
        MsgBox "Search:= " & Search & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If
    
End Sub

Dave

 

[ste33uka]

Sorry i forgot to say, when word is found, could it auto move to the cell and sheet ?

 

[dmt32]

Sorry i forgot to say, when word is found, could it auto move to the cell and sheet ?

"if someone could adjust it to just search the 1 sheet and 1 column"

My solution searches in the range on the specified single sheet as requested & also offers ability to continue searching for further matches.

are you saying you want to search multiple sheets like your original code or do you just want to select the found cell?

Dave

 

[ste33uka]

My solution searches in the range on the specified single sheet as requested & also offers ability to continue searching for further matches.

are you saying you want to search multiple sheets like your original code or do you just want to select the found cell?

Dave

Just want to search the one sheet and select cell when found

 

[mohadin]

Sub word()
    Dim srchVal As Variant
    Dim cel As Range
    Dim Flag As Boolean
    Flag = False
    '   Specify what to search for (hard-code or range reference)
    srchVal = InputBox("What value do you want to look for?")
    For Each cel In Sheets("sheet1").[S:S]
        If Format(cel.Value) = Format(srchVal) Then
            '            MsgBox srchVal & " found in " & cel.Address
            cel.Select
            Flag = True
        End If
        If Flag Then Exit Sub
    Next
End Sub

 

[dmt32]

Just want to search the one sheet and select cell when found

In that case just add the line shown in BOLD

Sub word()
    
    Dim Search      As String
    Dim FoundCell   As Range
    Dim ws          As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Data")
    
    'Specify what to search for
    Do
        Search = InputBox("What Do you want To search for?", "Search")
        'cancel pressed
        If StrPtr(Search) = 0 Then Exit Sub
    Loop Until Len(Search) > 0
    
    If FoundCell Is Nothing Then Set FoundCell = ws.Cells(1, "S")
startsearch:
    Set FoundCell = ws.Columns("S").Find(Search, After:=FoundCell, LookIn:=xlValues, lookat:=xlWhole)
    If Not FoundCell Is Nothing Then
        FoundCell.Select
        'match found
        If MsgBox("Search:= " & Search & Chr(10) & _
        "Match found On sheet " & ws.Name & " Cell " & FoundCell.Address & Chr(10) & Chr(10) & _
        "Continue Search?", 36, "Search") = vbYes Then GoTo startsearch
    Else
        'no match
        MsgBox "Search:= " & Search & Chr(10) & "Record Not Found", 48, "Not Found"
        
    End If
    
End Sub

This assumes that your sheet Data is the activesheet

Dave

 

[ste33uka]

problem maybe is that i might not be on the current sheet i want to search,
Would like to search the column on sheet, if i am on a different sheet