Searches

I would like to be able to search a specific column in a number of sheets in
the same workbook for values within a range and then either highlight the
cells or create a list of addresses - is this possible?

Peter Rump

Peter,

Yes, easily doable with a macro. Is that a viable option for you?

If so, give a few more quidelines about how you want to search, criteria,
etc.

HTH,
Bernie
MS Excel MVP


"peterrump" <peterrump@ntlworld.com> wrote in message
news:XQ3ie.7243$f5.3054@newsfe6-win.ntli.net...
> I would like to be able to search a specific column in a number of sheets
in
> the same workbook for values within a range and then either highlight the
> cells or create a list of addresses - is this possible?
>
> Peter Rump
>
>

Here's a basic macro that will scan col. A of each sheet and return the sheet
name and cell address if a cell contains a value >=1 and <=10:

Sub FindValues()

Dim sh As Worksheet
Dim cell As Range
Dim LowVal As Long
Dim HiVal As Long
Dim i As Integer
Dim j As Integer

Set sh = Worksheets.Add(before:=Worksheets(1))

LowVal = 1
HiVal = 10
j = 1

For i = 2 To ActiveWorkbook.Sheets.Count
For Each cell In Intersect(Sheets(i).[A:A], Sheets(i).UsedRange)
With cell
If IsNumeric(.Value) Then
If .Value >= LowVal And .Value <= HiVal Then
sh.Cells(j, "A").Value = Sheets(i).Name
sh.Cells(j, "B").Value = .Address(False, False)
j = j + 1
End If
End If
End With
Next
Next

End Sub

---
HTH
Jason
Atlanta, GA

"peterrump" wrote:

> I would like to be able to search a specific column in a number of sheets in
> the same workbook for values within a range and then either highlight the
> cells or create a list of addresses - is this possible?
>
> Peter Rump
>
>
>

Yes - I want to identify all dates in col J by either month or quarter and
ideally copy the whole row in which they appear (A-J) to a separate sheet.
The date entry format is yymmdd.

Thanks
Peter
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uBGv0mjWFHA.1508@tk2msftngp13.phx.gbl...
> Peter,
>
> Yes, easily doable with a macro. Is that a viable option for you?
>
> If so, give a few more quidelines about how you want to search, criteria,
> etc.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "peterrump" <peterrump@ntlworld.com> wrote in message
> news:XQ3ie.7243$f5.3054@newsfe6-win.ntli.net...
> > I would like to be able to search a specific column in a number of
sheets
> in
> > the same workbook for values within a range and then either highlight
the
> > cells or create a list of addresses - is this possible?
> >
> > Peter Rump
> >
> >
>
>

Thanks for the code - I have entered it but have a 'run time error 424
object required' message in the line 'For Each cell In Intersect etc' - any
idea what I have done wrong?

Petr Rump
"Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
news:28429BC8-E6DC-4024-B3DA-740D3CCFAFF4@microsoft.com...
> Here's a basic macro that will scan col. A of each sheet and return the
sheet
> name and cell address if a cell contains a value >=1 and <=10:
>
> Sub FindValues()
>
> Dim sh As Worksheet
> Dim cell As Range
> Dim LowVal As Long
> Dim HiVal As Long
> Dim i As Integer
> Dim j As Integer
>
> Set sh = Worksheets.Add(before:=Worksheets(1))
>
> LowVal = 1
> HiVal = 10
> j = 1
>
> For i = 2 To ActiveWorkbook.Sheets.Count
> For Each cell In Intersect(Sheets(i).[A:A], Sheets(i).UsedRange)
> With cell
> If IsNumeric(.Value) Then
> If .Value >= LowVal And .Value <= HiVal Then
> sh.Cells(j, "A").Value = Sheets(i).Name
> sh.Cells(j, "B").Value = .Address(False, False)
> j = j + 1
> End If
> End If
> End With
> Next
> Next
>
> End Sub
>
> ---
> HTH
> Jason
> Atlanta, GA
>
> "peterrump" wrote:
>
> > I would like to be able to search a specific column in a number of
sheets in
> > the same workbook for values within a range and then either highlight
the
> > cells or create a list of addresses - is this possible?
> >
> > Peter Rump
> >
> >
> >