If I select range A2:f2 how can I ask Excel to loop through the individual cells in this range using VBA?
|Check out our Excel VBA Resources|
You do not need to select the cells to loop through them.
To just loop through the range, use something like this:
For Each c In Worksheets("Sheet1").Range("a2:f2").Cells
c.Value = "KLP"
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:
c = ActiveWindow.RangeSelection.Address
For Each r In Worksheets("Sheet1").Range(c).Cells
r.Value = "aa"
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.
If i wanted to declare the variables below eg c and r in your examples, do i class them as range variables?
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.
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?