VBa nd excel

[Stephen Warker]

Is there a way to increment the column with vba code. I need to move A1 to B1 to whatever a variable is.

Thanks and any help will be greatly appriciated.

Steve

[jp_schwartz]

Try to use the cells object instead of range. In the cells object, you use the col and the row (first col and row is 1). Most of the times that´s possible... but there are some things that you can do only with Range.

Use these functions:

Usage:
msgbox f_excelcol(0)
Returns "A"
These are cero-based functions.

Function f_ExcelCol(lng_J As Long) As String

Do While lng_J > 256
lng_J = lng_J - 25
Loop

Select Case lng_J
Case Is <= 26
f_ExcelCol = s_ExcelLetter(Val(lng_J))
Case 27 To 52
f_ExcelCol = "a" & s_ExcelLetter(Val(lng_J))
Case 53 To 78
f_ExcelCol = "b" & s_ExcelLetter(Val(lng_J))
Case 79 To 104
f_ExcelCol = "c" & s_ExcelLetter(Val(lng_J))
Case 105 To 130
f_ExcelCol = "d" & s_ExcelLetter(Val(lng_J))
Case 131 To 156
f_ExcelCol = "e" & s_ExcelLetter(Val(lng_J))
Case 157 To 182
f_ExcelCol = "f" & s_ExcelLetter(Val(lng_J))
Case 183 To 208
f_ExcelCol = "g" & s_ExcelLetter(Val(lng_J))
Case 209 To 234
f_ExcelCol = "h" & s_ExcelLetter(Val(lng_J))
Case 235 To 256
f_ExcelCol = "i" & s_ExcelLetter(Val(lng_J))
End Select

f_ExcelCol = UCase(f_ExcelCol)

End Function

Function s_ExcelLetter(lng_J As Long) As String

Do While lng_J > 25
lng_J = lng_J - 25
Loop

If lng_J = 0 Then
s_ExcelLetter = "a"
ElseIf lng_J = 1 Then
s_ExcelLetter = "b"
ElseIf lng_J = 2 Then
s_ExcelLetter = "c"
ElseIf lng_J = 3 Then
s_ExcelLetter = "d"
ElseIf lng_J = 4 Then
s_ExcelLetter = "e"
ElseIf lng_J = 5 Then
s_ExcelLetter = "f"
ElseIf lng_J = 6 Then
s_ExcelLetter = "g"
ElseIf lng_J = 7 Then
s_ExcelLetter = "h"
ElseIf lng_J = 8 Then
s_ExcelLetter = "i"
ElseIf lng_J = 9 Then
s_ExcelLetter = "j"
ElseIf lng_J = 10 Then
s_ExcelLetter = "k"
ElseIf lng_J = 11 Then
s_ExcelLetter = "l"
ElseIf lng_J = 12 Then
s_ExcelLetter = "m"
ElseIf lng_J = 13 Then
s_ExcelLetter = "n"
ElseIf lng_J = 14 Then
s_ExcelLetter = "o"
ElseIf lng_J = 15 Then
s_ExcelLetter = "p"
ElseIf lng_J = 16 Then
s_ExcelLetter = "q"
ElseIf lng_J = 17 Then
s_ExcelLetter = "r"
ElseIf lng_J = 18 Then
s_ExcelLetter = "s"
ElseIf lng_J = 19 Then
s_ExcelLetter = "t"
ElseIf lng_J = 20 Then
s_ExcelLetter = "u"
ElseIf lng_J = 21 Then
s_ExcelLetter = "v"
ElseIf lng_J = 22 Then
s_ExcelLetter = "w"
ElseIf lng_J = 23 Then
s_ExcelLetter = "x"
ElseIf lng_J = 24 Then
s_ExcelLetter = "y"
ElseIf lng_J = 25 Then
s_ExcelLetter = "z"
End If

End Function

[Nitro]

If you can explain more, I think I can help you.