#1   Report Post  
Old May 16th 05, 05:26 PM
peterrump
 
Posts: n/a
Default 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



  #2   Report Post  
Old May 16th 05, 06:07 PM
Bernie Deitrick
 
Posts: n/a
Default

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




  #3   Report Post  
Old May 16th 05, 06:42 PM
Jason Morin
 
Posts: n/a
Default

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



  #4   Report Post  
Old May 23rd 05, 06:21 PM
peterrump
 
Posts: n/a
Default

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






  #5   Report Post  
Old May 23rd 05, 09:02 PM
peterrump
 
Posts: n/a
Default

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







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to dinamic cell that searches for "value1 & value2" in table. armindo Excel Discussion (Misc queries) 3 February 4th 05 10:59 PM
FIND formula that searches from right? KellyB Excel Worksheet Functions 1 January 21st 05 10:51 PM
which formula or function searches for a value in a range of cell. Roccobarocco Excel Worksheet Functions 5 December 3rd 04 12:06 PM
which formula or function searches for a value in a range of cell. Roccobarocco Excel Worksheet Functions 7 December 3rd 04 09:06 AM


All times are GMT +1. The time now is 04:40 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017