Results 1 to 3 of 3

Thread: VBa nd excel

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2000
    Posts
    23
    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
    Stephen Warker

  2. #2
    Hyperactive Member
    Join Date
    Nov 2000
    Location
    Mexico City
    Posts
    306

    Cool

    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
    If things were easy, users might be programmers.

  3. #3
    Fanatic Member
    Join Date
    Jan 2000
    Location
    Nitro
    Posts
    633
    If you can explain more, I think I can help you.
    Chemically Formulated As:
    Dr. Nitro

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width