MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Loop through range of cells


Posted by CPE on December 20, 2001 2:49 AM

I want to loop through a selected range of cells.

If I select range A2:f2 how can I ask Excel to loop through the individual cells in this range using VBA?

Many Thanks!

Chris

Posted by Robb on December 20, 2001 4:08 AM

Chris

You do not need to select the cells to loop through them.

To just loop through the range, use something like this:

Sub LoopCells()
For Each c In Worksheets("Sheet1").Range("a2:f2").Cells
c.Value = "KLP"
Next c
End Sub

In this instance, the code will simply fill each cell with KLP.

If you really want to loop through cells you have selected, try this instead:

Sub LoopSelection()
Worksheets("Sheet1").Select
c = ActiveWindow.RangeSelection.Address
For Each r In Worksheets("Sheet1").Range(c).Cells
r.Value = "aa"
Next r
End Sub

You will need to select cells on sheet1 for this to work - it will
just fill each selected cell with aa.

Try the code and then adjust it to do what you want.

Any help?

Regards

Robb

Posted by CPE on December 20, 2001 4:53 AM

Thats great Robb

If i wanted to declare the variables below eg c and r in your examples, do i class them as range variables?

Posted by . on December 20, 2001 6:10 AM

Yes

If i wanted to declare the variables below eg c and r in your examples, do i class them as range variables?

Posted by Robb on December 21, 2001 1:41 AM

Chris

It varies between the procedures as I inadvertently changed the types.

In LoopCells, c would be declared as a Range.

In LoopSelection, however, I used c as the Address of the RangeSelection and so it
should be declared as a String. In this procedure, r is the Range object.

Hope that it's not too confusing. I suppose that, for clarity, it would have been better
if I had used c as the Range in both.

Regards

Robb

Thats great Robb If i wanted to declare the variables below eg c and r in your examples, do i class them as range variables?