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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,478
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

solution allows you do that but to select the found cell you will need to activate the sheet

Add the line of code shown in BOLD just above the FoundCell.Select statement

Rich (BB code):
      ws.Activate
        FoundCell.Select

Dave
 
Solution

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
358
Office Version
  1. 2019
Platform
  1. Windows
Thanks for everyones help with this
job done
 

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

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