CODEWITHSUNDEEP
excelvba
Dominic
Dominic

Reputation: 69

How to reference a new cell in a table by column name

I have a working subroutine that inserts a value into a cell, as part of a new row of a table. I'm referencing the cell to insert the data into by the column number. However, I'd like to be able to use the column/header name from the table, not a number, so that table columns can be moved without upsetting the cell references in the macro. Does anyone know how to do this?

Here is my sample code.

Sub InsertValue(ByRef iTargetCell As Integer, ByRef CellData As String, ByRef strType As String)

  Select Case strType
    ' Define the data as Text
      Case "T"
      ' Specify the number format to be used for a particular column in the new row
        NewRow.Range.Cells(1, iTargetCell).NumberFormat = "@"
      ' Insert the data for a particular column into the new row
        NewRow.Range.Cells(1, iTargetCell).Value = CellData
    ' Define the data as a number
      Case "N"
        NewRow.Range.Cells(1, iTargetCell).NumberFormat = "0"
        NewRow.Range.Cells(1, iTargetCell).Value = CellData
  End Select
End Sub

The variable NewRow is already publicly defined.

Public NewRow As ListRow

It is set in another subroutine.

Set NewRow = Range(strTableName).ListObject.ListRows.Add(AlwaysInsert:=True)

Upvotes: 0

Views: 1793

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

ActiveSheet.ListObjects("Table1").ListColumns("ColName").Index

will return the position of the column with header "ColName" in the table

Upvotes: 1

Related Questions