Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page creating a named row for dynamic addressing creating a named row for dynamic addressing
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
Old 07-04-2018, 12:54 PM
trevorc trevorc is offline creating a named row for dynamic addressing Windows 7 32bit creating a named row for dynamic addressing Office 2013
Competent Performer
creating a named row for dynamic addressing 		 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default creating a named row for dynamic addressing
Hi All,
I am currently using the code below to create various pages of user data, I am currently doing this by selecting the Column letter as a reference to the data I need EG name, date, serial number, etc...
I would like to know how to insert columns in between these already set.



All the data is in a table, how can I stop using the column header as a reference, and use the table column name in it's place, I can then insert extra column anywhere without having to rewrite my code each time.


Code:
Sub button_click4() ' delivery docket
    If InRange(ActiveCell, Range("A4:A5000")) Then
        Sheets("Delivery Docket").Range("B13") = Range("i" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("B14") = Range("g" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("B15") = Range("h" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("C16") = Range("j" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("H13") = Range("i" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("H14") = Range("g" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("H15") = Range("h" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("I16") = Range("j" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("C22") = "Serial/C-bus # " & Range("e" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("C23") = "RMA # " & Range("a" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("J6") = Range("a" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("J8") = Range("a" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Delivery Docket").Range("J5") = Date
        Sheets("Delivery Docket").Range("J9") = Date
        Sheets("Delivery Docket").Select
        Exit Sub
    End If
End Sub
Sub button_click1()
    If InRange(ActiveCell, Range("A4:A5000")) Then
        Sheets("Payment Advice").Range("B3") = Date
        Sheets("Payment Advice").Range("B4") = Range("a" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B5") = Range("g" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B6") = Range("h" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B7") = Range("i" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B8") = Range("j" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B9") = Range("k" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B14") = Range("d" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B15") = Range("e" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B18") = Range("u" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B23") = Range("AC" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B24") = Range("AB" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B25") = Range("AD" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Range("B26") = Range("AE" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Payment Advice").Select
       Exit Sub
    End If
End Sub
Sub button_click2()
    If InRange(ActiveCell, Range("A4:A5000")) Then
        Sheets("Address Label").Range("C3") = Range("i" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Address Label").Range("C5") = Range("h" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Address Label").Range("C7") = Range("j" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Address Label").Range("C4") = Range("g" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Address Label").Range("C6") = Range("k" & Mid(ActiveCell.Address, 4, 4))
        'Sheets("Address Label").Range("B14") = Range("d" & Mid(ActiveCell.Address, 4, 4))
        'Sheets("Address Label").Range("B15") = Range("e" & Mid(ActiveCell.Address, 4, 4))
        Sheets("Address Label").Select
        Exit Sub
    End If
End Sub
Reply With Quote
  #2  
Old 07-05-2018, 03:31 PM
p45cal's Avatar
p45cal p45cal is offline creating a named row for dynamic addressing Windows 10 creating a named row for dynamic addressing Office 2016
Expert
  
Join Date: Apr 2014
Posts: 866
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
  #3  
Old 07-05-2018, 03:35 PM
p45cal's Avatar
p45cal p45cal is offline creating a named row for dynamic addressing Windows 10 creating a named row for dynamic addressing Office 2016
Expert
  
Join Date: Apr 2014
Posts: 866
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.


The rest of this suggestion depends on
Quote:
Originally Posted by trevorc View Post
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.
Reply With Quote
  #4  
Old 07-05-2018, 03:45 PM
trevorc trevorc is offline creating a named row for dynamic addressing Windows 7 32bit creating a named row for dynamic addressing Office 2013
Competent Performer
creating a named row for dynamic addressing 		 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default
That's perfect, just what I needed to make adding extra columns a breeze
Reply With Quote
  #5  
Old 07-05-2018, 03:51 PM
p45cal's Avatar
p45cal p45cal is offline creating a named row for dynamic addressing Windows 10 creating a named row for dynamic addressing Office 2016
Expert
  
Join Date: Apr 2014
Posts: 866
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
Quote:
Originally Posted by trevorc View Post
That's perfect, just what I needed to make adding extra columns a breeze
I've updated my response in my last message since you replied.
Reply With Quote
  #6  
Old 07-05-2018, 06:39 PM
trevorc trevorc is offline creating a named row for dynamic addressing Windows 7 32bit creating a named row for dynamic addressing Office 2013
Competent Performer
creating a named row for dynamic addressing 		 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default
Thanks for your help.
Is it also possible to put these 2 lines of code globally/public so I don't have to repeat them in all Sub's.
Code:
        Set tblColumns = ActiveCell.ListObject.ListColumns
        Set ACRow = ActiveCell.EntireRow
Reply With Quote
  #7  
Old 07-06-2018, 02:43 AM
p45cal's Avatar
p45cal p45cal is offline creating a named row for dynamic addressing Windows 10 creating a named row for dynamic addressing Office 2016
Expert
  
Join Date: Apr 2014
Posts: 866
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
Quote:
Originally Posted by trevorc View Post
Is it also possible to put these 2 lines of code globally/public so I don't have to repeat them in all Sub's.
Sure but if you've got multiple tables you'll need to update tblcolmns anyway, and if the active cell is likely to move row, which by definition it is, your going to have to update that too.
Reply With Quote
  #8  
Old 07-08-2018, 02:17 AM
trevorc trevorc is offline creating a named row for dynamic addressing Windows 7 32bit creating a named row for dynamic addressing Office 2013
Competent Performer
creating a named row for dynamic addressing 		 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default
Hi,
Thanks for your help on this. I'll do it the way shown for now. Just one minor addition to my code... I know i can use vba to print a sheet, nut can I do that if the sheet is hidden.
So instead of selecting the sheet ofter updating the fields, just print it.
regards
Trevor
Reply With Quote
  #9  
Old 07-08-2018, 03:21 AM
p45cal's Avatar
p45cal p45cal is offline creating a named row for dynamic addressing Windows 10 creating a named row for dynamic addressing Office 2016
Expert
  
Join Date: Apr 2014
Posts: 866
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
If a sheet is hidden it has to be unhidden before printing, however briefly. This process can be largely hidden from the user with the likes of:
Code:
Application.ScreenUpdating = False
With Sheets("Sheet1")
  originalVisibility = .Visible
  .Visible = xlSheetVisible
  .PrintOut
  .Visible = originalVisibility
End With
Application.ScreenUpdating = True
where the sheet is restored to its original visibilty after the print instruction.
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode


Similar Threads
Thread Thread Starter Forum Replies Last Post
creating a named row for dynamic addressing 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 named row for dynamic addressing Creating a dynamic summary sheet FenelonPaul Excel 5 09-23-2015 07:11 AM
creating a named row for dynamic addressing 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:50 PM.

Contact Us - Privacy Statement - Top

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