Reputation: 3659
I have an excel sheet that looks like this;
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
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
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
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