For Each "cell" in Range - question

[TheRobsterUK]

I currently have a macro which looks like this:

Please Login or Register  to view this content.

Which works all well and good. However, this code also gives the same result:

Please Login or Register  to view this content.

So my question is why does the second sub give the same result??? I thought you had to dimension the range then refer to each cell as "cell" but it works with any word by the looks of it (cabbage???). Can someone explain what's going on in the sub, why does the "For Each cabbage" actually work?

Cheers
Rob

Ps - currently using Excel 2010

[HaHoBe]

Hi, Rob,

as soon as you place Option Explicit at the top of the module the second code and cabbage would be marked by the debugger.

Anyhow, why not use

Please Login or Register  to view this content.

Ciao,
Holger

[Norie]

Rob

Both sets of code do exactly the same thing.

You can call the loop variable anything you want, within reason, and it will work, unless you have Option Explicit at the top of the module.

Without Option Explicit you don't need to declare your variables.

[TheRobsterUK]

Ah okay I get it now. So would you guys write the code more like this:

Please Login or Register  to view this content.

So you dimension two variables? One as the range and one as the cell? Or would you do something else?

I'd rather dimension everything correctly (use Option Explicit) as it's good practice and it means I have to understand the code properly.

Cheers

[HaHoBe]

Hi, TheRobsterUK,

for me IŽd only use one variable like

Please Login or Register  to view this content.

and only work with a second one for the original range if it would not be static like here. Still my favourite would be the one-liner.

I'd rather dimension everything correctly (use Option Explicit) as it's good practice and it means I have to understand the code properly.

Please have a look at the Reddick-convention or Hungarian notation for more info on that topic.

Please Login or Register  to view this content.

Here the variable "tells" a story of what kind of variable it is and what could be expected from it.

Ciao,
Holger

[TheRobsterUK]

Thanks for the replies HaHoBe, most helpful. For my spreadsheet the range is likely to be dynamic so I will have to use a variable to define it rather than Range("A1:A10") which was just an example, but yes I understand that using your method would be simpler if the range was static.

One final thing - this also works:

Please Login or Register  to view this content.

This also works! However the MyCell range is actually larger than the MyRange range so why does the syntax still work? I am confused as to how the computer reads this...why I need a variable to iterate through the cells in a defined range, yet that variable itself can be a single cell or a range bigger than the one I'm iterating through?

Maybe I'm overthinknig this...

[HaHoBe]

Hi, TheRobsterUK,

with

Please Login or Register  to view this content.

youŽre assigning a range to the variable but when using it as the variable in the For...Next loop it will get assigned to the cells within the range and loose the information you originally assigned to it. Test it by yourself with

Please Login or Register  to view this content.

Ciao,
Holger

[TheRobsterUK]

Ah okay so the important thing is that (with Option Explicit on) that I created the MyCell variable, but I take it Excel is clever enough to know that it needs to change the range assigned to the variable so that it can use it in the For...Next loop. I.e. it just needs it to be a suitable type of variable, the range information associated with it originally does not matter as this is lost?

Cheers
-Rob

[HaHoBe]

Hi, Rob,

maybe just decide to assign ranges to variables for action which might be monitored as a total while you use different ones for the loops which will get assigned there and sort of overwite the contents of the variable each time.

Ciao,
Holger

[Norie]

Rob

VBA isn't really doing anything clever, its just reusing the variable.