VBa return previous column letter 1

[knifey]

Hi,
Does anyone have a VBA function which can accept a column number (or letter) and return the column letter of the previous column?
I've written some code but am running into problems when the column letters go into double digits (e.g. previous column letter to AA should be Z).
Can anyone advise?
Thanks,
K

 

[PHV]

A starting point:

Function getPrevCol(myCol) As Integer
getPrevCol = Columns(myCol).Column - 1
End Function

Note: myCol may be either a column number or a column letter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

If you want to convert a column number to a letter:

Function getColLetter(myCol As Integer) As String
If myCol > 0 And myCol <= Columns.Count Then
  getColLetter = Split(Columns(myCol).Address(False, False), ":")(0)
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[N1GHTEYES]

And if you want to go the other way (column letters to number) which you gave as an optional input, you could try this.

Public Function GetLetterNumber(coltext As String) As Long
Dim textlen As Long, i As Long
coltext = UCase(coltext)
textlen = Len(coltext)
For i = textlen To 1 Step -1
    letternum = Asc(Mid(coltext, i, 1)) - 64
    If letternum < 1 Or letternum > 26 Then
        GetLetterNumber = -1
        Exit Function
    Else
        GetLetterNumber = GetLetterNumber + ((letternum) * (26 ^ (textlen - i)))
    End If
Next i
End Function

Which is nowhere near as cute as PHVs code but it does the job.

Note that the above will return -1 if you give it a column address containing anything other than letters.

Tony