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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
StephenJRCommented:
Duh, thanks ssaqibh, somehow I forgot one could have more than 26 columns.
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
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
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.