define Previous Column Letter to function

excel 2003 vba

what I need:
In the code below the (wrange) is a public string
I'm using the input box to get the Column and cell
Public wrange As String
Public Function MyRange()

wrange = UCase(InputBox("What is the Intersect of Column And Row: Example -  A2 "))

MsgBox (wrange)

end function

--------------------------------------------------
Later In the bit below
I'm extracting the Column Letter...

What I need:


If the    "str =   J"  Column Letter  is   J

I need to find a way to determine the previous Letter...in this case  "I"


' CURRENT CODE
' EXTRACTS THE COLUMN LETTER AWAY FROM CELL NUMBER
Dim str As String
   Dim str2 As String
   Dim falpha As String
   
    str = wrange
      str2 = str

If IsNumeric(Mid(str2, 2, 1)) Then
falpha = Left(str2, 1)
Else
falpha = Left(str2, 2)
End If

str = falpha



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
krishnakrkcCommented:
Hi,

Try this one.

@ssaqibh

Your code would also fails for a range AAB2

Kris
Public wrange As String
Public Function MyRange()
Dim r As Range
wrange = UCase(InputBox("What is the Intersect of Column And Row: Example -  A2 "))

On Error Resume Next
Set r = Range(CStr(wrange))
On Error GoTo 0

If Not r Is Nothing Then
    If r.Column > 1 Then
        MyRange = Split(Cells(1, r.Column - 1).Address(1, 0), "$")(0)
    End If
End If
Debug.Print MyRange
End Function

Open in new window

0
 
StephenJRCommented:
This perhaps, if s1="J" will give I:

Chr(Asc(s1) - 1)
0
 
Saqib Husain, SyedEngineerCommented:
StephenJR

your code will not work with two- or three-letter columns


fordraiders,

Try this code

Dim str As String
   Dim str2 As String
   Dim falpha As String
str = Wrange
str2 = Range(Wrange).Offset(0, -1).Address(0, 0)
   
If IsNumeric(Mid(str2, 2, 1)) Then
falpha = Left(str2, 1)
Else
falpha = Left(str2, 2)
End If
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
StephenJRCommented:
Duh, thanks ssaqibh, somehow I forgot one could have more than 26 columns.
0
 
Saqib Husain, SyedEngineerCommented:
Yes you are right, Kris, but it will fail in only those cases where the OP's original code (which gives current column) fails. I was simply updating the given code to the OP's requirement.

Saqib
0
 
FordraidersAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.