For Each Loop Problem

[Andy_new]

I am reading a book and there is an example about adding formula into comments. I tried to run the code in my VBA but I found it the For Each loop did not work. Only the currently selected cell would be processed And after the code Next cell/Next Worksheet / Next Window, all these would be come empty(shown by debugger). I am using the excel 2007. What is the problem here?

The code:


Sub note()
For Each Window In Windows
For Each Worksheet In Window.SelectedSheets
For Each cell In Application.Selection
addr = Worksheet.Name & "!" & cell.Address
temp = ""
On Error Resume Next
temp = Range(addr).Comment.Text
If InStr(temp, "|") Then
temp = Mid(temp, InStr(temp, "|") + 1)
End If
Range(addr).ClearComments
If Range(addr).HasFormula = True Then
Range(addr).AddComment (cell.Formula & "|" & temp)
Else
If temp <> "" Then Range(addr).AddComment temp
End If
Next cell
Next Worksheet
Next Window
End Sub

[Frosty]

Sub note()
For Each Window In Windows
For Each Worksheet In Window.SelectedSheets
For Each cell In Application.Selection
addr = Worksheet.Name & "!" & cell.Address
temp = ""
On Error Resume Next
temp = Range(addr).Comment.Text
If InStr(temp, "|") Then
temp = Mid(temp, InStr(temp, "|") + 1)
End If
Range(addr).ClearComments
If Range(addr).HasFormula = True Then
Range(addr).AddComment (cell.Formula & "|" & temp)
Else
If temp <> "" Then Range(addr).AddComment temp
End If
Next cell
Next Worksheet
Next Window
End Sub

try and use the VBA tags or CODE tags... makes it a lot easier to read. Without testing your code, since it clearly needs to be run on something other than an empty spreadsheet, I would guess that it is running on the only cell you have selected, because it says "For Each Cell in Application.Selection"

So if you only have one cell selected, then it's only going to work on that single cell.

If you only have one worksheet selected... it's only going to work on the one selected cell in that worksheet. Etc.

Doesn't look to me like there's anything wrong with the for each loop structure... but then I don't have the item to test on that I presume you'd have. Did the book not tell you to select a range of cells before running the macro and multiple worksheets?

If your question is about how For...Each loops work... they are not that much different in concept than For....Next loops


Dim x as Integer
For x = 1 to Selection.Cells.Count
Selection.Cells(x).Value = "something"
Next

vs.


Dim oCell as Cell
For Each oCell in Selection.Cells
oCell.Value = "something"
Next
The difference is that instead of iterating through an index (a number designating the individual cells), you are iterating through a collection of objects (the Cells collection). There are advantages and disadvantages to both approaches.

[Andy_new]

Oops... Thanks for editing my post.
Referring to the question, my book simply explains the code without a proper example. Yes you are right that I only select a cell to work on so I got such result.
What I was thinking is that, the code was trying to do the action for every cell in every worksheet in every window, but it turned out not to be.
So is there another way to do what I want? That is select all the cell by the VBA program but not by myself.

Thanks for you kind reply.:content:

[Frosty]

Yes, but you're going to get yourself into trouble if you start using For Each Loops on the cells within the Selection, and then automatically select all of the available cells in each open worksheet.

I think there are 65,000 rows, and available columns that go out I don't know how far. That's a pretty big number of cells to go through, and you'll feel like you're caught in an endless loop.

The best way to proceed (if the book is no good) is to use the Record Macro feature... and get a feel for the objects you're using to do the stuff you want to do.

But, again, I don't think you're going to want to automatically select 65,000 cells times however many columns... and then iterate through those cells 1 by 1.

[Andy_new]

Haha I see your point. I think that yours is the same as what the book is trying to say.
Thanks again!

[Frosty]

Sure thing. One more point-- it would probably be good for you to know that CTRL + BREAK will pause the execution of any code you're running, if you have the project the code is in open.

That will save you from having to crash Excel (or whatever Office application you're in) in order to save yourself from whatever infinite (or close enough) loop you've managed to create for yourself.

Grin.