Announcement

Collapse
No announcement yet.

Convert a column number to a column letter

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert a column number to a column letter

    Hello everyone,

    I ned to convert a column letter to a column number for the following line of code in my VBA application:

    Code:
    strAverageBaseFormula = "=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":K" & Trim(Str(lngFirstRowOfIncrement + 1)) & ")"
    I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in teh middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?

    Thanks!
    Bryan
    Last edited by bdonkersgoed; November 22nd, 2005, 01:14.

  • #2
    Re: Convert a column number to a column letter

    Someone must know this?

    Basically, how do I convert "11" to "K"??

    Thanks,
    Bryan
    Last edited by bdonkersgoed; November 22nd, 2005, 01:50.

    Comment


    • #3
      Re: Convert a column number to a column letter

      Code:
      If MyColumn > 26 Then
              MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65)
          Else
              MyColumnLetter = Chr(MyColumn + 64)
          End If

      Comment


      • #4
        Re: Convert a column number to a column letter

        Bryan

        Why not just use R1C1 notation for the formula?
        Boo!

        Comment


        • #5
          Re: Convert a column number to a column letter

          One way,
          =AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":" & _ 
          Left(Cells(1, 11).Address(False, False), Len(Cells(1, 11).Address(False, False)) - 1) & Trim(Str(lngFirstRowOfIncrement + 1)) & ")"

          Cheers
          Andy

          Comment


          • #6
            Re: Convert a column number to a column letter

            Originally posted by tonyrosen
            Code:
            If MyColumn > 26 Then
                    MyColumnLetter = Chr(Int((MyColumn - 1) / 26) + 64) & Chr(((MyColumn - 1) Mod 26) + 65)
                Else
                    MyColumnLetter = Chr(MyColumn + 64)
                End If
            Hehehe . . . thanks! I actually just wrote code almost identical to this, and was about to post it :P I thought there should be an easier way though.

            Thanks again!
            Bryan

            Comment


            • #7
              Re: Convert a column number to a column letter

              Originally posted by norie
              Bryan

              Why not just use R1C1 notation for the formula?
              Hmmmm . . . I guess that would have been the logical choice. I never use that notation though, and the spreadsheet that I am creating is for people who would not understand R1C1 at all.

              Thanks though!
              Bryan

              Comment


              • #8
                Re: Convert a column number to a column letter

                Bryan

                The formula would appear in whatever notation the workbook was set to show.
                Boo!

                Comment


                • #9
                  Re: Convert a column number to a column letter

                  Originally posted by norie
                  Bryan

                  The formula would appear in whatever notation the workbook was set to show.
                  Oh, I didn't knwo that . . . thanks

                  Too bad I just wrote a bunch of useless code then

                  Regards,
                  Bryan

                  Comment


                  • #10
                    Re: Convert a column number to a column letter

                    her is the easyest way, witch wil work with any number big

                    Code:
                    Public Function ColLett(Col As Integer) As String
                        
                        If Col > 26 Then
                            ColLett = ColLett((Col - (Col Mod 26)) / 26) + Chr(Col Mod 26 + 64)
                        Else
                            ColLett = Chr(Col + 64)
                        End If
                        
                    End Function

                    Comment


                    • #11
                      Re: Convert a column number to a column letter

                      It think this is easier.
                      Code:
                      Function ColLetterFromNo(lngColNum) As String
                             ColLetterFromNo = Split(Cells(1, lngColNum).Address, "$")(1)
                      End Function
                      Boo!

                      Comment


                      • #12
                        Re: Convert a column number to a column letter

                        Similarly
                        Code:
                        Function GetColLetter(ByVal Col As Long) As String
                            GetColLetter = Replace(Cells(1, Col).Address(0, 0), 1, "")
                        End Function

                        Comment


                        • #13
                          Re: Convert a column number to a column letter

                          norie and jindon,

                          I'm sure I can take your code and use it with what I'm trying to accomplish, but I must admit I'm struggling a bit to understand what exactly it all means. The reason I address both of you is because I don't fully understand either one. I'll explain what I don't understand with each.

                          norie:
                          OK, so it seems like it should be splitting the cell reference, but what is a delimiter, represented by the "$"? And can you explain the (1) after you've already closed the Split?

                          jindon:
                          I understand the Replace one even less. Great, so I get the cell reference...but as soon as it gets past the last "s" in Address, I have no idea what all of that is supposed to do.

                          This may be a super simple question that seems easy to you, but I hope you understand my questions and that you are able to explain.

                          Thanks!

                          Comment


                          • #14
                            Re: Convert a column number to a column letter

                            You will understand if you get Help from vb help file about the parameters of Address property.

                            0 is equivalent to False.

                            Comment


                            • #15
                              Re: Convert a column number to a column letter

                              Lets try again here guys.

                              PersonHere,

                              Jindon has suggested you check the help file for the address property. This is a perfectly reasonable suggestion and you should do the same for the replace function. If you still don't understand then please be more specific about what you don't understand or why the suggestion didn't help.
                              Reafidy

                              Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                              Comment

                              Working...
                              X