Easily iterate through the columns?

T

Tippy

I would appreciate any advice on how to iterate through all the columns
easily. Currently, I am using the Asc(-character- ) to iterate to the
next column. However, I run into a problem after column Z, since now
the columns are not labeled with just one character (i.e. next column
is labeled "AA"). To clarify, I have posted a snippet of my code:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For t = Asc("C") To Asc("F") + numIter + 4
Results.Range(Chr(t) & 2 & ":" & Chr(t) & numPlanes + 1).Value =
Null
Next t
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

This code will only work up to column Z, then it will error since the
column Chr(Asc(Z) + 1) does not exist.

Thanks.
 
K

Kris

Tippy,

Use the cells property. Cells takes as arguments a row
number and a column number, so you do not have to use the
column letter.

Like:

For t = 3 To 6 + numIter + 4
Results.Range(cells (2, t), _
cells (numPlanes + 1, t)).Value = Null
Next t
 
T

Tippy

Kris,

I used your code in mine and it is still giving me the same error.

For t = 3 To 6 + numIter + 4
Results.Range(Cells(2, t), Cells(numPlanes + 4, t)).Value = Null
Next t

Am I using it incorrectly?

Thanks for your help.
Tipp
 
T

Tom Ogilvy

Dim rng as Range
for t = 3 to 50
set rng = Cells(1,t).Resize(1,8).entireColumn
msgbox rng.Address
Next

might give you an idea on how to use a different approach
 
T

Tom Ogilvy

Not if Results isn't the active sheet

For t = 3 To 6 + numIter + 4
Results.Range(Results.cells (2, t), _
Results.cells (numPlanes + 1, t)).ClearContents
Next t
 
T

Tippy

Tom, that fixed it, thanks! Is there a function that converts colum
number to it's letter equivalency
 
J

JWolf

Sub test()
Dim colnum As Integer
Dim colname As String
colnum = ActiveCell.Column
If Int(colnum / 26) = 0 Then
colname = Chr(colnum + 64)
Else
colname = Chr(Int(colnum / 26) + 64)
colname = colname + Chr(colnum Mod 26 + 64)
End If
MsgBox "Active Column is: " & colname
End Sub
 
G

Gord Dibben

Tippy

One of each.......

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

usage is =GetColNum("A") returns 1

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

usage is =GetColLet(1) returns A

Gord Dibben Excel MVP
 
Top