for each cell In Selection
cell.Value = "do somethin"
next
Russell Harper wrote:
>
> How do I iterate through all the selected cells, if the selection isn't
> contiguous? The code below works for rectangular selections, but if the
> selection isn't, it just "makes up" a rectangle with the equivalent
> area.
>
> Sub Iterate()
>
> Dim lngWorking As Long
>
> For lngWorking = 1 To Selection.Cells.Count
> With Selection.Cells(lngWorking)
> 'Cell specific code here
> End With
> Next
>
> End Sub
>
> Any assistance would be appreciated...
>
> Russell
>
> rha...@intouchsurvey.com
Continuing with Charlie's reply, this will work for Excel 97
and up but I don't think it will work for Excel 95, where you
would have to also cycle through each range. Example in
join.htm on my site; otherwise, your code in Excel 97 and up.
Sub RH_Iterate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lngWorking As Long
Dim cell As Range
lngWorking = 0
For Each cell In Selection
lngWorking = lngWorking + 1
With cell
'Cell specific code here
.Value = .Address(0, 0) & "-" & lngWorking
End With
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Your modified example will show left to right by row
through each range.
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Charlie <Hang...@thisNg.net> wrote in message
news:39F7851B...@thisNg.net...
David McRitchie
That's great! I'm not sure how truly useful it will be, but its pretty
cool. Now quit playing and get back to work before your boss catches you!
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Russell Harper" <russ...@sympatico.ca> wrote in message
news:uQpQrd0PAHA.199@cppssbbsa04...
> Thanks, it works great. My problem was that I was looking for a Cell
> variable type, seeing none, I couldn't figure out how to declare a cell
> to iterate in a For ... Each loop. Here's some code I was using to test
> it - hint: you'll get the best results if the cells are set squarer than
> usual...
>
> Sub DoIt()
>
> Dim cell As Range
>
> For Each cell In Selection
>
> Select Case Rnd()
>
> Case Is < 0.5
>
> With cell.Borders(5)
> .ColorIndex = xlNone
> .LineStyle = xlNone
> End With
>
> With cell.Borders(6)
> .ColorIndex = xlAutomatic
> .LineStyle = xlContinuous
> .Weight = xlMedium
> End With
>
> Case Is > 0.5
>
> With cell.Borders(5)
> .ColorIndex = xlAutomatic
> .LineStyle = xlContinuous
> .Weight = xlMedium
> End With
>
> With cell.Borders(6)
> .ColorIndex = xlNone
> .LineStyle = xlNone
> End With
>
> End Select
>
> Next
>
> End Sub
>
> Russell
> ...
>
>