Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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


Check out our Excel VBA Resources

Re: Loop through range of cells

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


Re: Loop through range of cells

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?


Yes

Posted by . on December 20, 2001 6:10 AM
If i wanted to declare the variables below eg c and r in your examples, do i class them as range variables?


Yes and no - Re: Loop through range of cells

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?


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.