Here's the code, nothing complex just a little math:
Thanks,
Seth Rowe
Private Function GetColumn(ByVal FieldNumber As Integer) As String
On Error GoTo Err_Handler
' This Function converts the FieldNumber into an Excel Lettered
Column
' Given a range it adds/subtracts a set integer to the
FieldNumber
' Creating the ASCII Number representing the desired column,
then converts
' The ASCII Number back to text using Chr and returns that
value
Select Case FieldNumber
' Columns A to Z
Case 0 To 25
GetColumn = Chr(FieldNumber + 97)
' Column AA to AZ
Case 26 To 51
GetColumn = "A" & Chr(FieldNumber + 71)
' Column BA to BZ
Case 52 To 77
GetColumn = "B" & Chr(FieldNumber + 45)
' Column CA to CZ
Case 78 To 103
GetColumn = "C" & Chr(FieldNumber + 19)
' Column DA to DZ
Case 104 To 129
GetColumn = "D" & Chr(FieldNumber - 7)
' Column EA to EZ
Case 130 To 155
GetColumn = "E" & Chr(FieldNumber - 33)
' Column FA to FZ
Case 156 To 181
GetColumn = "F" & Chr(FieldNumber - 59)
' Column GA to GZ
Case 182 To 207
GetColumn = "G" & Chr(FieldNumber - 85)
' Column HA to HZ
Case 208 To 233
GetColumn = "H" & Chr(FieldNumber - 111)
' Column IA to IV (Max Excel Column)
Case 234 To 255
GetColumn = "I" & Chr(FieldNumber - 137)
' Table has to many columns raise error
Case Else
MsgBox("The query returned more columns than could be
inputted into Excel." & vbCr & vbCr & _
"Please narrow down the query and try again.", ,
_
"To many columns.")
' Use the following setting in the calling sub to trap
this error
GetColumn = "-1"
End Select
Exit_Handler:
Exit Function
Err_Handler:
MsgBox(Err.Description)
Resume Next
End Function
rowe_newsgroups wrote:
For i As Integer = Asc("A") To Asc("Z")
.Range(Chr(i) & rowNum.ToString()).Select()
Next
This is the best way if you don't need to go past column Z. To handle
that you could build a function that will return a column letter based
on the value of i. The function I wrote for a vb6 program just had a
Select Case statement that examined the range an integer was in, added
the neccesary amount to get it to correspond to a letter's ascii value,
and then returned the chr(...) string - nothing to complex. Then the
for loop turns into this:
For i as integer = 1 to 256 ' (or whatever the max column count is)
.Range(GetColumn(i) & rowNum.ToString()).Select()
Next
I'll post the function's code tomorrow - it's on my work computer.
Thanks,
Seth Rowe
Arthur Dent wrote:
One possib is that you can use the Ascii codes... to loop, as follows:
For i As Integer = Asc("A") To Asc("Z")
.Range(Chr(i) & rowNum.ToString()).Select()
Next
<ja**********@hotmail.comwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.com...
>
But what if I wanted to point to columns? Any way of making a for loop
that goes through the letters of the alphabet? That is, something
like..
>
For i = A To Z
.Range("i" & row#).Select
Next i
>