Microsoft Office Forums use a named column in table

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-21-2019, 02:22 PM
trevorc trevorc is offline use a named column in table Windows 7 32bit use a named column in table Office 2013
Competent Performer
use a named column in table
 
Join Date: Jan 2017
Posts: 117
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 08-22-2019, 02:48 PM
p45cal p45cal is offline use a named column in table Windows 10 use a named column in table Office 2016
Expert
 
Join Date: Apr 2014
Posts: 282
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 08-26-2019, 08:40 AM
ArviLaanemets ArviLaanemets is offline use a named column in table Windows 8 use a named column in table Office 2016
Expert
 
Join Date: May 2017
Posts: 467
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

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].
Reply With Quote
Reply

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
use a named column in table 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
use a named column in table 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


All times are GMT -7. The time now is 03:16 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft