# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Chr(Asc(s1) - 1)
0
EngineerCommented:
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

If IsNumeric(Mid(str2, 2, 1)) Then
falpha = Left(str2, 1)
Else
falpha = Left(str2, 2)
End If
0
Commented:
Duh, thanks ssaqibh, somehow I forgot one could have more than 26 columns.
0
Commented:
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
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

EngineerCommented:
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
Author Commented:
Thanks
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.