Thread: [Solved] creating a named row for dynamic addressing
View Single Post
 
Old 07-05-2018, 03:31 PM
p45cal's Avatar
p45cal p45cal is offline Windows 10 Office 2016
Expert
  
Join Date: Apr 2014
Posts: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
First, the likes of:
Mid(ActiveCell.Address, 4, 4)
can be more reliably written as:
Activecell.row
but you don't need it.

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 on multiple lines you can make things easier with the likes of:
Code:
    Set tbl = ActiveCell.ListObject
        Sheets("Payment Advice").Range("B3") = Date
        Sheets("Address Label").Range("C5") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr1").Range)
        Sheets("Payment Advice").Range("B4") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr2").Range)
        Sheets("Payment Advice").Range("B5") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr3").Range)
        Sheets("Payment Advice").Range("B6") = Intersect(ActiveCell.EntireRow, tbl.ListColumns("hdr4").Range)
where I've invented imaginary header names hdr1, hdr2 etc.


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