Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

convert column letter designation to a number.

0

I am using the following code to find a specific column that has a list of data in it. My problem is that "ThisCol" is returned as a letter and I need to convert it to a number (Or if I could use the letter in a cell address variable that would be okay too) Thanks!

Dim ThisPos As Range

    With Range("A1:az100")

        Set ThisPos = .find(What:="Cosmic Debris", lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

        If Not ThisPos Is Nothing Then

            Cell_Add = Split(ThisPos.Address, "$")

            ThisCol = Cell_Add(1)

            ThisRow = Cell_Add(2) 

        End If

    End With••••ˇˇˇˇ

Answer
Discuss

Discussion

Thank you, I am learning a little more everyday. Your answer made what I was trying to do infinitely easier. 
baddog1016 (rep: 12) Sep 26, '20 at 10:30 pm
Add to Discussion

Answers

0
Selected Answer

Please look up the Range object. Every Range has the same properties. You will find them all listed and explained at the linked MSDN site. You will also find them listed in VBE's Object Browser.

Among the properties every Range has are the Address, the Row and the Column. Row and Column are numbers. The Address is a string like $A$4. Bear in mind that a Range can be a single cell or any number of cell together. This is why another property is the Count property which tells you how many cells there are. There are also the Rows and Columns properties (plural) which return collections of rows and columns and have their own Item and Count properties. This enables queries like MyRange.Rows.Count which returns a number and MyRange.Rows(1) which returns a Range. Don't forget that every range has the same properties. Therefore MyRange.Rows(1).Row will return 1.

Back to your question: Your Find function returns a Range. You called it ThisPos. You asked for ThisPos.Address which returns a string. (ThisPos.Address(0, 0) would return a string without the $ signs.) ThisPos.Column would give you the column number, ThisPos.Row it's row number. You can construct circular references, like Range(ThisPos.Address).Column. They serve no useful purpose. ThisPos.Column would return the same number.

Columns(10).Address or Range("I:I").Column enable conversion between letter and number but you are right: the letter is really of little use in VBA programming because the number is so much more flexible.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login