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" 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 |
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 |
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 |
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