Excel - iterating through ALL selected cells

Message has been deleted

Charlie

unread,

Oct 25, 2000, 3:00:00 AM10/25/00

to

Why not:

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

David McRitchie

unread,

Oct 25, 2000, 3:00:00 AM10/25/00

to

Hi Russell,
Please do not post to more than one group. The best choice was
microsoft.public.excel.programming

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

Message has been deleted

David McRitchie

unread,

Oct 26, 2000, 10:07:14 AM10/26/00

to

Hi Russell, (generates a random labyrinth)
Couldn't figure out what you doing with borders
until I ran it. Neat, something for mouse. A keeper.

David McRitchie

Chip Pearson

unread,

Oct 26, 2000, 10:54:51 AM10/26/00

to

Russell,

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