By using t2do.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

T2Do.com

 


Create a List Add to top of List Add all to top Add to bottom Edit List Comment Add a Checklist Add list Counter Import/Add many Export List Formatting Modify a List Delete a List
Ascending Descending No Sort
Add ---------- Show list only Show everything Show tags Hide tags
Nav Overview Top Up Show BreadCrumbs Shortcuts Subheader Random Search Last 100 items you added Last 100 modified Public Lists Popular Lists
Create/View to-do List Upcoming Deadlines Add a deadline Time balance List Daily Top 50
How to View Shortcuts Add a Shortcut System shortcuts Saving time with Tags Date/time Time Tracking Shortener AutoHotKeys
Time is your most valuable resource k Link Track Task from List Name Track via shortcut To-do list and time tracking Timer-Creator.com Time Tracking Spreadsheet based Time Tracking Set up @Time Tracking list Reports on how you spent your time Today Popup Current task Window
Random select Shuffle list Random Book Title How to use the Random Tag Random Domain Name Random Book Title Random List Item
To-Do List Add to to-do Set Goals Balance your time Track your time Create User defined header Create Top 10 List Ideas Habits Questions Creativity Accomplished Problems Learn Health Misc Lists
Work to-do Work Terms Work Accomplishments Work 1 Pagers Weekly Status Reports Work Checklists Work Rules Work test
Weight Loss Terms Accomplishments 1 Pagers Health Weekly Status Reports Checklists Rules
-->

Convert a column number to a letter - ColumnLetter( )


Function ColumnLetter(c)
' c is the numerical col
a = Split(Cells(1, c).Address, "$")
ColumnLetter = a(1)

End Function


Prerequisites
  • Functions
  • Parameters
  • Comments
  • variables
  • Assignment
  • VBA Split function
  • Cells collection
  • Address property
  • Arrays
  • Returning a function value

    How the function works
    The columnLetter function is passed a number of the column and returns the column letter. For example, A is column 1, Z is column 26, AA is column 27 and so on.

    Cells(1,c) will access the column passed to the function so if 27 is passed the code will become cells(1,27).

    The .address property will convert cells(1,27) row 1 column 27 to an Address, which becomes $AA$1.

    The Split VBA function will split the string $AA$1 into an array named a.

    The array will have a(0) = ""; a(1) = "AA" and a(2) = "1".

    The value in a(1) is returned which is the column converted from a number to a letter.

    This routine will fail under a number of circumstances:
  • Option Base 1 is set
  • Column passed is greater than 16K
  • Hasn't been tested on versions other than Excel 2016 but should work


    •