CODEWITHSUNDEEP
excelvba
pranavrules
pranavrules

Reputation: 167

Reference a cell by its column name

I want to update the contents of a cell in a workbook. My code looks a little like this:

ProductionWorkBook.Sheets("Production Schedule").Cells(StartRow, 1).Value = EstJobName(i)

The cells are referenced using Cells(StartRow, 1) Where StartRow was a pre-declared and pre-defined integer variable that specifies the row and "1" denotes the column.

EDIT: Now, I want to change this code to reference the columns by the column HEADERS instead.

For example, the header of a column is: "Fab Hours Date", how do I reference that?

Upvotes: 3

Views: 31200

Answers (4)

Vicky
Vicky

Reputation: 13244

Yes, you can simply use the letter name for the column in quotes:

Cells(StartRow, "A")

Edited to answer your further question: to look for a specific column name, try this:

columnNamesRow = 1           ' or whichever row your names are in
nameToSearch = "Fab Hours"   ' or whatever name you want to search for
columnToUse = 0
lastUsedColumn = Worksheets("Foo").Cells(1, Worksheets("Foo").Columns.Count).End(xlToLeft).Column

For col = 1 To lastUsedColumn
   If Worksheets("Foo").Cells(columnNamesRow, col).Value = nameToSearch Then
      columnToUse = col
   End If
Next col


If columnToUse > 0 Then
' found the column you wanted, do your thing here using "columnToUse" as the column index
End If

Upvotes: 3

Daniel
Daniel

Reputation: 13122

Here are two different functions to get what you want. To use them, you'd have to put them in your code.

Function ColumnNumberByHeader(text As String, Optional headerRange As Range) As Long
    Dim foundRange As Range
    If (headerRange Is Nothing) Then
        Set headerRange = Range("1:1")
    End If

    Set foundRange = headerRange.Find(text)
    If (foundRange Is Nothing) Then
        MsgBox "Could not find column that matches header: " & text, vbCritical, "Header Not Found"
        ColumnNumberByHeader = 0
    Else
        ColumnNumberByHeader = foundRange.Column
    End If
End Function

Function ColumnNumberByHeader2(text As String, Optional headerRange As Range) As Long
    If (headerRange Is Nothing) Then
        Set headerRange = Range("1:1")
    End If
    On Error Resume Next
    ColumnNumberByHeader2 = WorksheetFunction.Match(text, headerRange, False)
    If Err.Number <> 0 Then
        MsgBox "Could not find column that matches header: " & text, vbCritical, "Header Not Found"
        ColumnNumberByHeader2 = 0
    End If
    On Error GoTo 0
End Function

Example Calls:

 ColumnNumberByHeader ("Extn")
 ColumnNumberByHeader("1718", Range("2:2"))

Or in your case: 

ProductionWorkBook.Sheets("Production Schedule"). _
Cells(StartRow, ColumnNumberByHeader("Fab Hours Date")).Value = EstJobName(i)

Upvotes: 2

Peter Albert
Peter Albert

Reputation: 17475

ProductionWorkBook.Sheets("Production Schedule").Range("E"& StartRow).Value = ...

will do the job.

Though keep in mind that using hard coded references like the column letter will risk that the macro breaks when the sheet is edited (e.g. a column is inserted). It's therefore better to use a named range and Offset to access:

ProductionWorkBook.Sheets("Production Schedule").Range("StartCell").Offset(StartRow-1).Value

Now you only need to provide the name StartCellto your fist cell (make sure that it's a local name in the Name Manager)

Upvotes: 0

Dan
Dan

Reputation: 45752

ProductionWorkBook.Sheets("Production Schedule").Range("A" & StartRow).Value = EstJobName(i)

Unless you mean the column is a named range you defined?

Upvotes: 1

Related Questions