ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Searches (https://www.excelbanter.com/excel-worksheet-functions/26332-searches.html)

peterrump

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



Bernie Deitrick

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" wrote in message
...
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





Jason Morin

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(befo=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




peterrump

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
...
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" wrote in message
...
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







peterrump

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" wrote in message
...
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(befo=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







All times are GMT +1. The time now is 01:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com