Selecting Columns in Excel

I have a vbs script that is placing data into multiple rows and columns
using loops and referencing cells via a row number and column number.
Is there an easy way to change the column number into the Excel
'Letter' format? ie: 1=A, 2=B I need to select the column within my
script and it does not seem to like numbers, just the letter.

' this works
oExcel.Columns("A:A").Select

' this causes an error
oExcel.Columns("1:1").Select


Any help is appreaciated.

Dale

Mark
Mon Mar 20 07:29:13 CST 2006

Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

or

Function Num2Let(L As Long) As String
Dim s0 As String, s1 As String, S2 As String, s3 As String
If L > 18278 Then s0 = Chr((Int((L - 18279) / 17576) Mod 26) + 65)
If L > 702 Then s1 = Chr((Int((L - 703) / 676) Mod 26) + 65)
If L > 26 Then S2 = Chr(Num2Let & (Int((L - 27) / 26)) Mod 26 + 65)
s3 = Chr(((L - 1) Mod 26) + 65)
Num2Let = s0 & s1 & S2 & s3
End Function


Regards
Mark Dormer

"DaleM" <dmenken2@comcast.net> wrote in message
news:1142631306.942427.278260@i39g2000cwa.googlegroups.com...
>I have a vbs script that is placing data into multiple rows and columns
> using loops and referencing cells via a row number and column number.
> Is there an easy way to change the column number into the Excel
> 'Letter' format? ie: 1=A, 2=B I need to select the column within my
> script and it does not seem to like numbers, just the letter.
>
> ' this works
> oExcel.Columns("A:A").Select
>
> ' this causes an error
> oExcel.Columns("1:1").Select
>
>
> Any help is appreaciated.
>
> Dale
>

DaleM
Thu Mar 23 14:13:46 CST 2006

Thanks for getting me past the coder block. I couldn't get your code
to correctly render the Excel column letters via a number but your code
got me to this which seems to be working. Thanks again.

Function Num2CHR(tmpnum)
Dim alpha, digit1, digit2

digit1 = tmpnum
digit2 = 0
Do While digit1 > 26
digit2 = digit2 + 1
digit1 = digit1 - 26
Loop
alpha = Chr(digit1 + 64)
If digit2 > 0 Then alpha = Chr(digit2 + 64) + alpha
Num2CHR = alpha
End Function

For x = 1 To 82
WScript.Echo "[" & x & "] = " & Num2CHR(x)
Next