CODEWITHSUNDEEP
vbaexcel
Mark
Mark

Reputation: 7586

VBA - iterating over column names

I am doing the same type of operation across multiple columns. For example, my code currently works when I do this:

Cells(lRow, "D") = Cells(lRow, "D") + Cells(lRow + 1, "D")
Cells(lRow, "E") = Cells(lRow, "E") + Cells(lRow + 1, "E")
' .... '
Cells(lRow, "AA") = Cells(lRow, "AA") + Cells(lRow + 1, "AA")
Cells(lRow, "AB") = Cells(lRow, "AB") + Cells(lRow + 1, "AB")

In the code above, I am going from column D to column AB and at each column am adding the value of the current row I'm looking at with the value of the row below it. The result code is almost 30 lines long and it's mostly copy and pasted which is just nasty.

I am trying to refactor this code so that I can factor out the column name (the second argument in the Cells function call). I was thinking of using a for loop, iterating from column "D" to column "AB" and performing the addition operation. 

' iterate from column D to column AB and at each column do the operation below ' 
For c = "D" To "AB"
    Cells(lRow, c) = Cells(lRow, c) + Cells(lRow + 1, c)
Next c

This doesn't work because it seems VBA does not allow iteration (for loop) over characters (going from "D" to "AB").

I've seen other solutions involving integers and converting them to characters (A = 1, B = 2, C = 3, D = 4, etc.) but couldn't get that working. I think that looks something like this:

' integer to character mapping: A = 1, B = 2, C = 3, D = 4, ... , AA = 27, AB = 28 '
For i = 4 To 28
    colNameFromInt = Chr(ascii)
    Cells(lRow, colNameFromInt) = Cells(lRow, colNameFromInt) + Cells(lRow + 1, colNameFromInt)
Next i

In short, if I showed you the ~30 lines of code from the top, how would you condense it?

Thanks!

Upvotes: 0

Views: 3744

Answers (2)

L42
L42

Reputation: 19737

You can iterate through the Range and then just use Offset Property.
Something like:

Dim cel As Range
For Each cel In Range("D1:AB1")
    cel.Offset(lrow - 1, 0) = cel.Offset(lrow - 1, 0) + cel.Offset(lrow, 0)
Next

Upvotes: 2

teylyn
teylyn

Reputation: 35990

Columns can be addressed with a number. Since you are already using Cells() just use the column number instead of the letter

Dim c As Integer
For c = 5 To 10
    Cells(1, c) = "this is column " & c
Next c

This will change the cells in columns E to I.

Or to shorten the code in your initial example

Dim c As Integer
Dim lrow As Long
lrow = 2 ' or however you arrive at a value for this variable

For c = 4 To 28
    Cells(lrow, c) = Cells(lrow, c) + Cells(lrow + 1, c)
Next c

Upvotes: 3

Related Questions