I needed to alternate between columns A and B when writing to a bunch of rows in VBA. I created a variable, lStartCol, that I intended to alternate between zero and one and use in an Offset property.
lStartCol = rCell.Row Mod 2
rCell.Offset(lStartCol, 0).Value = “Some number”
rCell.Offset(lStartCol, 0).Value = “Some number”
I thought that was pretty clever, but I discovered that the first row may not always be the same. The entry in the first row has to start in column A, so that wouldn’t work. By the way, rCell is the iterative component in a For Each loop. I ended up with this
lStartCol = Abs(lStartCol – 1)
I put this at the end of the loop. lStartCol starts at zero (because I initialize there before the loop), so the first iteration stays in column A. Then it subtracts one, takes the absolute value and lStartCol become 1. Sure, I could have
If lStartCol = 1 Then
lStartCol = 0
Else
lStartCol = 1
End If
lStartCol = 0
Else
lStartCol = 1
End If
but that would be a cop out.
Hi Dick,
Is that not a Row offset you have used?
rCell.Offset(lStartCol, 0).Value = “Some number”
Here is an example you your newly discovered NOT knowledge.
Sub AlternateCols()
Dim rCell As Range
Dim lngCol As Long
‘ odd row start
For Each rCell In Range(“A1:A10?)
lngCol = Not lngCol
rCell.Offset(0, 1 + lngCol) = rCell.Address
Next
‘ even row start
lngCol = 0
For Each rCell In Range(“A20:A30?)
lngCol = Not lngCol
rCell.Offset(0, 1 + lngCol) = rCell.Address
Next
End Sub
In all honesty Dick the “cop out” would probably be the best, because it is easier to understand to someone else who might be reading your code.
I would take readability of ingenuity anyday and for those who say “no one else will ever see this code” have to remember that you one day may wish to go back and look at your code and be confused.
Bill: I agree. There’s just something inside me that hates to “waste” five lines when one will do the job. I can’t seem to remember that VBA line are free.
Andy: Yes, that should have been .Offset(0,lStartCol)
This is the simplest, but less intuitive:
lStartCol = 1 – lStartCol
Noodle it in your head a few times to see that it works. Because it doesn’t jump out at you, this calls for a comment like “toggle between columns 0 and 1?.
And, yes, I vote for a one-line solution (with comments) over a five-liner. Unless you’re paid by the line
I remember helping a junior programmer who had taken over some of my code. He double-spaced the comments and added all sorts of useless blank lines. Some times you could only see two lines of code on a screen! Drove me nuts.
Dave