#1
|
|||
|
|||
use a named column in table
Hi all,
I have a table with headers, how can I change the code below to reference the column name in stead of the range of cells the same as when I use it to select the data from that Row. Any ideas appreciated, thanks. Code:
If Not Intersect(Target, Sh.Range("AM4:AM5000")) Is Nothing Then ' notes field ActiveCell.FormulaR1C1 = "Date " & Date & " , " & Intersect(ACRow, tblColumns("Contact Name").Range) & ", " & _ Intersect(ACRow, tblColumns("Company Name").Range) & vbCrLf & Intersect(ACRow, tblColumns("Email Address").Range) & " Phone " & _ Format(Intersect(ACRow, tblColumns("Phone").Range), "0000 000 000") & vbCrLf & "Name on Card " & _ Intersect(ACRow, tblColumns("Name on Card").Range) Exit Sub End If |
#2
|
||||
|
||||
As long as you've previously set ACRow to something and you've set tblColumns to be a ListObject's .ListColumns you're good to go.
|
#3
|
|||
|
|||
a) Define your table as Table. You can refer to Table columns dataranges using Table name (when referring to it outside of Table) and column header as column name (a Table header must be in single row) either in workdheet formulas and in VBA code. Whenever you edit column header in Table, this change is reflected in all worksheet formulas automatically (but you have to edit VBA);
b) Define datarange of every column of your table you want refer to as Named Range with name of Named Range equal to column header. The best solution will be to define them as Dynamic ones - i.e. Named Range adjusts atomatically whenever new rows are aded to table or deleted from it. To define Dynamic Named Ranges based on table, the table must have some column datarange which never is empty. One possibility is to have a column with formula which returns row number for every entry. In worksheet formulas you can refer to Defined name ´using it's name. In VBA, you can refer to defined name like YourNamedRange as [YourNamedRange]. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table named after first cell | bheemskerk | Word VBA | 14 | 05-15-2019 07:43 PM |
Code to find a named (bookmarked?) table, replicate a row or table, and delete a specified table. | kevinbradley57 | Word VBA | 9 | 09-21-2017 04:58 PM |
Replace a named table with text | kevinbradley57 | Word VBA | 2 | 08-31-2017 07:49 AM |
Is it possible to put a formula in a table column header to define the name of the column? | JacquesW | Excel | 3 | 05-08-2017 08:00 AM |
Need a Macro to go to column named price and ADD 16% to each field | okief1122 | Excel Programming | 4 | 09-06-2014 10:59 PM |