Displaying data

M

Mistysweep

Good morning everyone.

I have a number of worksheets all in the same workbook containing five or
six columns of data.

Is there a way of searching all of the worksheets for a value I enter
(perhaps on a seperate worksheet) so that the entire row(s) that contain that
value are returned?


Is this possible?
 
B

bgeier

Is this what you had in mind?
On "Sheet4" cell "a2" put what you want to look for.
The macro will loop through each sheet, highlighting the row the searc
string was found on and put the row number and sheet name on Sheet
columns 2 and 3 respectively


Sub FindSomething()

Dim intSheetCounter As Integer
Dim strFoundrow As String
Dim intPlaceRow As Integer

intPlaceRow = 2
For intSheetCounter = 1 To Worksheets.Count
Worksheets(intSheetCounter).Select
cells(1,1).select
Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells(2, 1).Value
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False
False).EntireRow.Select
strFoundrow
Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells(2, 1).Value
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False
False).Row
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRow, 3)
Worksheets(intSheetCounter).Name
ThisWorkbook.Worksheets("Sheet4").Cells(intPlaceRow, 2)
strFoundrow
intPlaceRow = intPlaceRow + 1
Next
End Su
 
M

Mistysweep

Tried it and keep getting a compile error - syntax error at :-

Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells(2,1).Value.

Any ideas?
 
B

bgeier

Cells.Find(ThisWorkbook.Worksheets("Sheet4").Cells(2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).EntireRow.Select

Check you have commas after
Value
ActiveCell
xlFormulas
xlWhole
xlByColumns
xlNext
False

I tested it on my machine again and it worked
 
Top