CODEWITHSUNDEEP
excelvba
Abdul Shiyas
Abdul Shiyas

Reputation: 401

Excel VBA: Can we refer a column by its name?

I want to refer a column by its header name.

Currently column is 4th one and header name is "Preference". And the column consists of "Yes" or "No"

5th column header is "Reason" And it is filled only when "Preference" column is "No"

My code is

Private Sub CommandButton1_Click()

Dim i As Integer
Dim MyWorksheetLastRow As Byte
Dim MyWorksheetLastColumn As Byte

MyWorksheetLastRow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
MyWorksheetLastColumn = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets(1).Cells(1, MyWorksheetLastColumn + 1).Value = "Result"
For i = 2 To MyWorksheetLastRow
If Cells(i, 4).Value = "Yes" Then
Cells(i, MyWorksheetLastColumn + 1).Value = Cells(i, 4).Value
Else: Cells(i, MyWorksheetLastColumn + 1).Value = Cells(i, 5).Value
End If
Next i

End Sub

What I want is instead of Cells(i,4) , I want to call the it by column header example: Cells(i,"Preference"). Because I won't the column number of "Preference" in prior. And I use excel vba because I have to deal 20-30 similar files.

Upvotes: 0

Views: 501

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6206

Here is a function to find the column for X instance. I have put a subroutine in there to call it as an example for you.

Sub ColInstanceExample()
Dim MyColInstance As Long
MyColInstance = ColInstance("Preference", 2) 'Pass in what you are searching for and the instance you want to return the column number for
If MyColInstance = 0 Then
    MsgBox "Not Found"
Else
    MsgBox "Found at column: " & MyColInstance
End If
End Sub

Function ColInstance(HeadingString As String, InstanceNum As Long)
Dim ColNum As Long
On Error Resume Next
ColNum = 0
For X = 1 To InstanceNum
    ColNum = (Range("A1").Offset(0, ColNum).column) + Application.WorksheetFunction.Match(HeadingString, Range("A1").Offset(0, ColNum + 1).Resize(1, Columns.Count - (ColNum + 1)), 0)
Next
ColInstance = ColNum
End Function

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6206

Further to my comments, if you want to do it direct, you would have to do this:

cells(i,Application.WorksheetFunction.Match("Preference", Range("1:1"), 0)).

Upvotes: 3

Related Questions