creating a named row for dynamic addressing

p45cal · #3 07-05-2018, 03:35 PM

First, the likes of:
Mid(ActiveCell.Address, 4, 4)
can be more reliably written as:
Activecell.row
but you don't need it.

The rest of this suggestion depends on

Quote:

Originally Posted by trevorc

All the data is in a table,

meaning that the data is in a real Excel table (a listobject in vba).

Let's say that column J on your sheet has the header "hdr7" for the table.
Then likes of:

Code:

Sheets("Address Label").Range("C5") = Range("j" & Mid(ActiveCell.Address, 4, 4))

can be:

Code:

Sheets("Address Label").Range("C5") = intersect(activecell.EntireRow,activecell.ListObject.ListColumns("hdr7").Range)

and since you'll be using activecell.ListObject.ListColumns and activecell.EntireRow on multiple lines you can make things easier with the likes of:

Code:

  Set tblColumns = ActiveCell.ListObject.ListColumns
  Set ACRow = ActiveCell.EntireRow
  Sheets("Payment Advice").Range("B3") = Date
  Sheets("Address Label").Range("C5") = Intersect(ACRow, tblColumns("hdr1").Range)
  Sheets("Payment Advice").Range("B4") = Intersect(ACRow, tblColumns("hdr2").Range)
  Sheets("Payment Advice").Range("B5") = Intersect(ACRow, tblColumns("hdr3").Range)
  Sheets("Payment Advice").Range("B6") = Intersect(ACRow, tblColumns("hdr4").Range)

where I've invented imaginary header names hdr1, hdr2 etc.

There'll many ways, this one not necessarily the slickest.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating Dynamic Ranking Tables	ssol2016	Excel	1	10-27-2016 08:47 AM
creating a code to create dynamic text in word	ah8471	Word VBA	1	12-29-2015 10:52 PM
Creating a dynamic summary sheet	FenelonPaul	Excel	5	09-23-2015 07:11 AM
Help Creating A Dynamic Footer	Mikemo	Word	3	02-05-2013 11:58 AM
Dynamic Named Ranges using text	hannu	Excel	0	06-22-2010 04:42 PM