Thread: Searches
View Single Post
  #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