CODEWITHSUNDEEP
excelnamed-rangesvba
Gonik
Gonik

Reputation: 11

Work with specific column in Named Range (Excel VBA)

Please have a look at this table, which I have named "Tasks":

enter image description here

It is a very basic GANTT chart-like table. Using VBA I use data from this table in order to perform some tasks in other Worksheets.

I have a For loop which loops through each row like this:

For i = 1 To Range("Tasks").Rows.Count
    Worksheets("Calendar").Cells(i,2).Value = Range("Tasks").Cells(i,2)
End For

There are many other operations within the For loop, but that goes beyond the scope of my question. Basically what this does is that it loops through the entire table and performs various operations and calculations (where applicable) which results in populating other cells in other worksheets.

My question is this:

Since all columns in the table are labeled, I would like to somehow reference the Column name instead of column number in the loop, if it is possible of course.

For example:

Worksheets("Calendar").Cells(i, 2).Value = Range("Tasks").Cells(i, "Title")

This helps for code readability since then I would know that this references the "Title" column, instead of going back and forth to see which column is e.g. the number "2".

I know that this kind of reference can be done in Excel by using

=Tasks[Title]

(e.g. This expression can be used for Data Validation)

Is it possible to reference columns like this? I am relatevely new to VBA, so I'm not quite sure.

Looking forward for your answer.

Upvotes: 1

Views: 9141

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

if it's an Excel Table, then you can use

  Dim lo As ListObject
  Dim col As ListColumn
  Dim r As Range

  Set lo = ActiveSheet.ListObjects("Tasks")
  Set col = lo.ListColumns.Item("Title")
  r = col.DataBodyRange

  For i = 1 To Range("Tasks").Rows.Count
      Worksheets("Calendar").Cells(i,2).Value = r.Cells(i)
  End For

Upvotes: 2

Related Questions