MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Looping through selected cells


Posted by Chris on September 11, 2001 3:00 AM

I have a number of cells selected as part of a macro. I then want to loop through each highlighted cell in turn and run some code against each cell.

How do I loop through each cell of a selected range?

Thanks

Chris


Posted by Jeff on September 11, 2001 5:00 AM


Dim cell as Range
For Each cell in Selection
'YOUR CODE FOR EACH CELL HERE
Next


Posted by JAF on September 11, 2001 5:00 AM

You'll need to set up a For/Next loop.

The code to do this is below. NB: in this example, the value of each cell in range("A1:A10") is increased by one each time the macro is run. You can of course change this section of the code to do what you want it to do.

in addition, you don't have to restrict the macro to a hard-coded range, you could use "For each i in Selection" which would perform the actions on whatever cells are currently highlighted.

Sub Cells_Loop()
Dim i As Range
For Each i In Range("A1:A10") 'or whatever your range is
i.Value = i.Value + 1 'or whatever your code is
Next i
End Sub

Hope this helps.
JAF