Search entire workbook for cell and return whole row.

  • Thread starter Thread starter junx13
  • Start date Start date
J

junx13

Hi,
I need to write function that takes in a value and searches
through my entire workbook which contains 4 worksheets. the function
will search each worksheet in only the second column and return all the
rows of the cell(s) in the second column which contain that value.

I am not very good at macros, and any help is greatly appreciated!!!
Thank you!!
 
Try this macro:

Sub ColumnBFind()

Dim ws As Worksheet
Dim r As String
Dim ws1 As String

For Each ws In Worksheets
r = ws.Range("B:B").Find(What:="myword", _
After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row

ws1 = ws.Range("B:B").Find(What:="myword", _
After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Worksheet.Name

MsgBox "Item Found In:" & Chr(10) & "Row: " _
& r & Chr(10) & "Sheet: " & ws1

Next ws

End Sub

--
Change the "myword" to what you're searching.

HTH
Jason
Atlanta, GA
 
hi thanks for the help ..... was wondering how I could pop up a
dialogbox to accept what I want to search for instead of just changing
the macrr? I.e wanna just press a button and search via my input??

Thanks again!

Junx
 
Just ran the macro without the userform, and there is an error 91

Object variable or With block variable not set.. what is the problem???
Anyone know?
 
Try this macro:

Sub ColumnBFind()

Dim ws As Worksheet
Dim r As String
Dim ws1 As String

For Each ws In Worksheets
r = ws.Range("B:B").Find(What:="myword", _
After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row

ws1 = ws.Range("B:B").Find(What:="myword", _
After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Worksheet.Name

MsgBox "Item Found In:" & Chr(10) & "Row: " _
& r & Chr(10) & "Sheet: " & ws1

Next ws

End Sub
Click to expand...
...

Your macro assumes there'd be one and only one match on each worksheet. If there
were more than one match on each worksheet, you're not following the OP's specs
which asked that the rows of all matching cells be returned. If there were no
matches on some (or all) worksheets, then your macro throws the runtime errors
the OP has already reported.

Also, why use a second .Find call to determine the worksheet name rather than
doing it directly with

ws1 = ws.Name

??!

The following is an overengineered alternative, but it's more flexible and more
robust.


Sub ColBFind()
Dim ws As Worksheet, rng As Range, c As Range
Dim ra As String, lca As String, n As Long

Static s As String


On Error Resume Next

Set rng = Application.InputBox( _
Prompt:="Select range to search", _
Title:="ColBFind", _
Type:=8 _
)

If rng Is Nothing Then
Exit Sub

Else
ra = rng.Address
lca = rng.Cells(rng.Cells.Count).Address

End If

On Error GoTo 0


s = InputBox( _
Prompt:="Enter string to find", _
Title:="ColBFind", _
Default:=s _
)

If s = "" Then Exit Sub


For Each ws In ActiveWorkbook.Worksheets
ActiveCell.Offset(n, 0) = "''" & ws.Name & _
"' - searching range " & ra & " for '" & s & "'"
n = n + 1

Set c = ws.Range(ra).Find( _
What:=s, _
After:=ws.Range(lca), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False _
)

If c Is Nothing Then
ActiveCell.Offset(n, 0).Value = String(4, " ") & _
"no matches found"
n = n + 1

Else
ActiveCell.Offset(n, 0).Value = String(4, " ") & _
c.Address(0, 0, xlA1, 1)
n = n + 1

Set rng = c

Do 'forever
Set c = ws.Range(ra).FindNext(After:=c)

If c.Address = rng.Address Then Exit Do

ActiveCell.Offset(n, 0).Value = String(4, " ") & _
c.Address(0, 0, xlA1, 1)
n = n + 1

Loop

End If

n = n + 1

Next ws

End Sub
 
Back
Top