Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify adjacency cells with same value
Is there a simple way to identify all the adjacency cells (in a row or
column) with the same value? I can obviously do this with a loop but I'm hoping there is a VBA function that might do this more quickly. Thanks, Paul DS. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify adjacency cells with same value
Paul D Smith wrote:
Is there a simple way to identify all the adjacency cells (in a row or column) with the same value? I can obviously do this with a loop but I'm hoping there is a VBA function that might do this more quickly. How about this? Sub matches() Dim found As Collection Set found = New Collection v = ActiveCell.Value If v = "" Then Exit Sub For Each cell In Rows(ActiveCell.Row).Cells If cell.Column ActiveCell.SpecialCells(xlCellTypeLastCell).Column _ Then Exit For If cell.Value < "" Then If cell.Column < ActiveCell.Column Then If v = cell.Value Then found.Add cell End If End If End If Next For Each cell In Columns(ActiveCell.Column).Cells If cell.Row ActiveCell.SpecialCells(xlCellTypeLastCell).Row Then _ Exit For If cell.Value < "" Then If cell.Row < ActiveCell.Row Then If v = cell.Value Then found.Add cell End If End If End If Next 'do what you need here, then... Set found = Nothing End Sub At the end of the sub, you have a collection called "found" that contains each cell that matches ActiveCell.Value. You can use that to do whatever you need -- for example, if you just need a list of addresses: For n = 1 To found.Count Debug.Print found.Item(n).Address Next Perhaps a bit over-complicated, but it works... -- Chainsaws are not generally accepted as an IT equipment must have. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify adjacency cells with same value
....snip...
That would certainly work but I was hoping to be able to avoid VBA loops if possible as I find them generally slow. My problem is slightly simpler than your general solution in that I know that: 1. I know the rows affected 2. All rows have data 3. All rows with identical data will be adjacent. Currently I use a loop which keeps walking the rows whilst the row data equals that in the first row (and then logically move the first row forward when I find a mismatch) but I was hoping there was a more elegant solution. Paul DS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify adjacency cells with same value
"Paul D Smith" wrote in message
... ...snip... That would certainly work but I was hoping to be able to avoid VBA loops if possible as I find them generally slow. My problem is slightly simpler than your general solution in that I know that: 1. I know the rows affected 2. All rows have data 3. All rows with identical data will be adjacent. Currently I use a loop which keeps walking the rows whilst the row data equals that in the first row (and then logically move the first row forward when I find a mismatch) but I was hoping there was a more elegant solution. Paul DS Looking on the web, using "=EXACT()" seems to be a way to do this. Basically set another column which says "Is this the same as the cell in the next row" and then work on the "False" values which indicate a change. Paul DS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify cells based on format | Excel Programming | |||
Identify Text on two cells | Excel Discussion (Misc queries) | |||
Tag/Identify Cells | Excel Programming | |||
How do I identify blank cells? | New Users to Excel | |||
how to identify cells containing formulas | Excel Programming |