Alternating Columns

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”

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
    lStartCol = 1
End If

but that would be a cop out.

Posted in Uncategorized

5 thoughts on “Alternating Columns

  1. 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

    ‘ even row start
    lngCol = 0
    For Each rCell In Range(“A20:A30?)
    lngCol = Not lngCol
    rCell.Offset(0, 1 + lngCol) = rCell.Address

    End Sub

  2. 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.

  3. 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.

  4. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.