software

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?

--
Many thanks

Mistysweep


Is this what you had in mind?
On quot;Sheet4quot; cell quot;a2quot; put what you want to look for.
The macro will loop through each sheet, highlighting the row the search
string was found on and put the row number and sheet name on Sheet 4
columns 2 and 3 respectivelySub 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(quot;Sheet4quot;).Cells (2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).EntireRow.Select
strFoundrow =
Cells.Find(ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells (2, 1).Value,
ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
False).Row
ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells(intPlaceRo w, 3) =
Worksheets(intSheetCounter).Name
ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells(intPlaceRo w, 2) =
strFoundrow
intPlaceRow = intPlaceRow 1
Next
End Sub--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=542057Tried it and keep getting a compile error - syntax error at :-

Cells.Find(ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells (2,1).Value.

Any ideas?

--
Many thanks

Mistysweepquot;bgeierquot; wrote:

gt;
gt; Is this what you had in mind?
gt; On quot;Sheet4quot; cell quot;a2quot; put what you want to look for.
gt; The macro will loop through each sheet, highlighting the row the search
gt; string was found on and put the row number and sheet name on Sheet 4
gt; columns 2 and 3 respectively
gt;
gt;
gt; Sub FindSomething()
gt;
gt; Dim intSheetCounter As Integer
gt; Dim strFoundrow As String
gt; Dim intPlaceRow As Integer
gt;
gt; intPlaceRow = 2
gt; For intSheetCounter = 1 To Worksheets.Count
gt; Worksheets(intSheetCounter).Select
gt; cells(1,1).select
gt; Cells.Find(ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells (2, 1).Value,
gt; ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
gt; False).EntireRow.Select
gt; strFoundrow =
gt; Cells.Find(ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells (2, 1).Value,
gt; ActiveCell, xlFormulas, xlWhole, xlByColumns, xlNext, False,
gt; False).Row
gt; ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells(intPlaceRo w, 3) =
gt; Worksheets(intSheetCounter).Name
gt; ThisWorkbook.Worksheets(quot;Sheet4quot;).Cells(intPlaceRo w, 2) =
gt; strFoundrow
gt; intPlaceRow = intPlaceRow 1
gt; Next
gt; End Sub
gt;
gt;
gt; --
gt; bgeier
gt; ------------------------------------------------------------------------
gt; bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
gt; View this thread: www.excelforum.com/showthread...hreadid=542057
gt;
gt;


Cells.Find(ThisWorkbook.Worksheets(quot;Sheet4quot;).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--
bgeier
------------------------------------------------------------------------
bgeier's Profile: www.excelforum.com/member.php...oamp;userid=12822
View this thread: www.excelforum.com/showthread...hreadid=542057