Return column letter

  • In my workbook, the VBA code writes SUMIF functions properly, then I use the FillRight property to drag the generated equations to the right. The problem is that, in the real data, sometimes the cells that would get the SUMIF function already have data themselves that needs to be included. Clearly, dragging the formula across will obliterate that data. I thought that I could write the VBA code to retain any existing data along with the SUMIF function, but instead of dragging it to the right I would have Excel simply write the function across using a different index key for the number of columns.

    My question is, right now my code has the following:



    I can hardcode Column I as the starting point because the first year will always be put in that column. I know I can insert a new index, say j, before the SUMIF function and write that function for each column that needs it, but how would I rewrite the code? The Column() function returns the column number; is there a function whose argument is the column number and will return the letter? I would like to have the function look something like:

    Code
    1. ...
    2. For j = 1 to years + 1
    3. Cells(begin, 9).Formula = "=sumif($A" & begin + 1 & ":$A" & endlevel & "," & i + 1 & "," & [B][COLOR=red]Column(8 + j)[/COLOR][/B] & begin + 1 & ":" & [COLOR=red][B]Column(8 + j)[/B][/COLOR] & endlevel & ")"
    4. Next j
    5. ...


    Of course, the ...FillRight command would go away.

    Any ideas? Thanks in advance. ozgrid.com/forum/core/index.php?attachment/36952/

  • Re: VBA Code - return column letter


    Code
    1. Public Function XL_ColToLetter(ColumnNumber As Integer) As String
    2. If ColumnNumber < 27 Then
    3. ' Columns A-Z
    4. XL_ColToLetter = Chr(ColumnNumber + 64)
    5. Else
    6. XL_ColToLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
    7. Chr(((ColumnNumber - 1) Mod 26) + 65)
    8. End If
    9. End Function
  • Re: VBA Code - return column letter


    Thanks, cytop. Would I write this function in a module, then call the function XL_ColToLetter() in the UserForm code? I'm not that familiar with custom functions and calling them.

  • Re: VBA Code - return column letter


    I'd use:

    Code
    1. For j = 1 to years + 1
    2. Cells(begin, 9).FormulaR1C1 = "=sumif(R[1]C1:R" & endlevel & "C1," & i + 1 & ",R[1]C:R" & endlevel & "C)"
    3. Next j

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: VBA Code - return column letter


    cytop, the formula worked great, but now I'm getting an "argument mismatch" error. I've copied and pasted the formula into a new project's module. I'm passing an integer into the formula through VBA but I'm getting an error. If I type the formula into Excel directly, I get a #NAME? error.


    However, if I open the original workbook I worked on back in March when you first gave this to me, it provides a letter value instead of the #NAME? error. I still get an error when running it through VBA. Is there some global setting I'm missing or some property value the module must have? It is listed as a "public function" both in the new workbook and the original workbook. Thoughts?

  • Re: VBA Code - return column letter


    FWIW, this version will work in later versions of Excel with more columns:

    Code
    1. Function ColToLetter(col As Long) As String
    2. ColToLetter = Split(Cells(1, col).Address, "$")(1)
    3. End Function

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why