Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to dinamic cell that searches for "value1 & value2" in table. | Excel Discussion (Misc queries) | |||
FIND formula that searches from right? | Excel Worksheet Functions | |||
which formula or function searches for a value in a range of cell. | Excel Worksheet Functions | |||
which formula or function searches for a value in a range of cell. | Excel Worksheet Functions |