CODEWITHSUNDEEP
excelvbaexcel-2016
user1315789
user1315789

Reputation: 3659

Find out Excel column character from name of cell in first row

I have an excel sheet that looks like this;

enter image description here

The header names are found in first row. I would like to have a VBA function that returns the column number, given the cell name in the first row. For example, getColumnNumber("B Symbol") should return B. getColumnNumber("C Symbol") should return C.

I am using Excel 2016.

Upvotes: 0

Views: 1045

Answers (3)

user1315789
user1315789

Reputation: 3659

This is the answer to my own question, modified from teddys'. There is a slight error in his answer.

With Worksheets(1).Range("a1:z1") ' 
    Set c = .Find("whatever you need to find", lookin:=xlValues) 
    If Not c Is Nothing Then 
        firstAddress = split(c.Address,"$")(1) 'should be 1, not 0 
        msgbox firstAddress 
    End If 
End With

Upvotes: 0

user4039065
user4039065

Reputation:

You don't need VBA for this. The following formula works just as well and any VBA is using essentially the same method.

=SUBSTITUTE(ADDRESS(1, MATCH("C Symbol", 1:1, 0), 4), 1, TEXT(,))

Upvotes: 1

teddy2
teddy2

Reputation: 390

Find the text and then read the address.

With Worksheets(1).Range("a1:z1") ' 
    Set c = .Find("whatever you need to find", lookin:=xlValues) 
    If Not c Is Nothing Then 
        firstAddress = split(c.Address,"$")(0) 
        msgbox firstAddress 
    End If 
End With

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel

Upvotes: 2

Related Questions