Macro search only 1 column of sheet

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Use below code:

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

VBA Code:
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
what about
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,478
Office Version
  1. 2019
Platform
  1. Windows
Hi,
you could use Range.Find Method to do what you want

VBA Code:
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

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,478
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,478
Office Version
  1. 2019
Platform
  1. Windows
Just want to search the one sheet and select cell when found

In that case just add the line shown in BOLD

Rich (BB code):
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

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,145
Messages
5,623,003
Members
415,945
Latest member
Koboca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top