CODEWITHSUNDEEP
excelvbscript
Jona
Jona

Reputation: 305

Excel - convert column letter to number equivalent vbs

I'm writing a vbs script to extract some data from an excel spreadsheet. Currently using the function:

objSheet.Cells(rowNum, colNum).Value

To get cell values, this allows me to do maths on the column number, e.g. add three to move across three columns. But in some instances I want to specify which Columns to get by letter:

objSheet.Cells(4, E).Value

I therefore need to write a vbs function to convert column letter to numbers E => 5. Needs to be able to handle a spreadsheet more than 26 cols wide.

I've seen lots of functions on the internet and SO for doing the opposite but not found much for doing the conversion this way.

Thanks

Upvotes: 1

Views: 6335

Answers (2)

Samy Geronymos
Samy Geronymos

Reputation: 405

without vba:

To convert a column number to a letter you enter this formula:

=LEFT(ADDRESS(1,3,4)) (returns the column letter for the cell C1=C)

But this is still not satisfactory because if you insert a column before the column C the number 3 won't change to 4, so here is a fix:

=LEFT(ADDRESS(1,COLUMN(C1),4))

Hope this helps.

This is especially helpful when you need to create strings that specify a cell range for example.

Upvotes: 0

Peter Albert
Peter Albert

Reputation: 17495

This code will run without Excel:

Function ColNum(strCol As String) As Integer
    Dim i As Integer, col As Integer

    For i = Len(strCol) To 1 Step -1
        col = col + (Asc(Mid(strCol, i, 1)) - 64) * (26 ^ (i - 1))
    Next
    ColNum = col
End Function

Alternatively, in Excel, you can simply use this:

Function ColNum(strCol As String) As Integer
    ColNum = Range(strCol & "1").Column
End Function

Upvotes: 5

Related Questions