Dim Months As Range
Dim MonthlySales As Range
Set Months = Range("Months")
'e.g, "Months" might be a named range referring to A1:A12
Set MonthlySales = Range("MonthlySales")
'e.g, "Monthly Sales" might be a named range referring to B1:B12
Dim Month As Range
For Each Month in Months
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")
Dim rng3 As Range
For Each rng3 in rng1
@brettdj: Your citation is correct, but you forgot to mention that it is followed by six "Except..." phrases. One of them being: "Except as a substitute for cell references in macro coding Always use Excel Names as a substitute for cell references when constructing macros. This is to avoid errors arising from the insertion of additional rows or columns whereby the macro coding no longer points to the intended source data."
Consider, if the above example had been written like this:
Here are a couple additional reasons to make liberal use of named ranges though I am sure I could think of more.
I agree with your development philosophy; however I think the paper is nonsense. It talks about how range names can confuse novices who are debugging spreadsheets, but anyone who uses novices to look at complex spreadsheets gets what they deserve! I used to work for a firm who reviewed financial spreadsheets, and I can tell you that it is not the sort of job you give to a novice.
If you had used named ranges to begin with, the Months and Sales columns could be moved around all you like, and your code will continue working just fine.
It is pretty obvious what the named ranges Months and MonthlySales contain, and what the procedure is doing.
One small point of emphasis I'll add to all the excellent answers given above:
Probably the biggest thing you can do to avoid using Select is to as much as possible, use named ranges (combined with meaningful variable names) in your VBA code. This point was mentioned above, but glossed over a bit; however, it deserves special attention.
The debate about whether named ranges are good or bad spreadsheet design continues - I'm firmly in the no camp. In my experience they increase errors (for standard users who have no need of code).
There is no meaningful debate. Anyone who argues against defined names has not taken the time to fully understand their ramifications. Named formulas may be the single most profound and useful construct in all of Excel.
This code will work just fine at first - that is until you or a future user decides "gee wiz, I think I'm going to add a new column with the year in Column A!", or put an expenses column between the months and sales columns, or add a header to each column. Now, your code is broken. And because you used terrible variable names, it will take you a lot more time to figure out how to fix it than it should take.
Why is this important? Partially because it is easier for other people to understand it, but even if you are the only person who will ever see or use your code, you should still use named ranges and good variable names because YOU WILL FORGET what you meant to do with it a year later, and you will waste 30 minutes just figuring out what your code is doing.