Find the next dynamic column letter

[Ivan Howard]

Good morning all,

Please could someone help me?

I'm using Excel 2016. I have a string variable that I manually populate with a column address letter, e.g. "S". Without accessing any worksheets, and without writing a massive If...ElseIf statement, I need some VBA that will allow me to get the next column letter. This could be anywhere in the first 150+ columns. Because I can't use "Activecell", I can't use Activecell.Offset... .

Any ideas?

Many thanks.

 

[Fluff]

What do you mean by "the next column"? How would the code determine which is the "next" column?

 

[Ivan Howard]

Hi Fluff, thanks for assisting.

I would like to populate one variable with a column letter e.g. "S" and get VBA to work out that the next column is "T"... or populate the variable with "BQ" and get VBA to work out that the next column is "BR". I have a set of macros that need to run on about 30 columns, but the start column is not always known. What I wanted to be able to do is, using an input box, get the user to insert the start column letter and then the macro would do the rest. I could rewrite the macros to make use of Activecell.Offset... but that would mean rewriting a lot of code. I just wondered if there was an easy way to determine the next column letter after the start one is given.

I was given the following function, but this uses numbers to work out the letter... my input is not numbers but letters. I hope this makes sense.

Public Function GetColumnLetter(ColumnNumber) As String

    If ColumnNumber <= 26 Then
        ' Columns A-Z
        GetColumnLetter = Chr(ColumnNumber + 64)
    Else
        GetColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & Chr(((ColumnNumber - 1) Mod 26) + 65)
    End If

End Function

Thanks again.

 

[Fluff]

Ok, you can do that like

   Dim Col As String
   
   Col = "BR"
   MsgBox Split(Cells(1, Col).Offset(, 1).Address, "$")(1)

 

[Ivan Howard]

That's awesome Fluff - thank you! Exactly what I need.

Have a great day!

 

[Fluff]

Glad to help & thanks for the feedback.