Maintaining Column References

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
I have attached a macro enabeled worksheet that will create a Word document based on the data entered into a row.

If a user inserts a column, the offsets get messed up, and so too will the word document.

How can I dynamically maintain the column references if this occurs.
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
Sorry, Here is the attachment.
 

Attachments

  • ColRef.zip
    21 KB · Views: 19

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
If I was concerned about my users mucking with the sheet, what I would do is:
  • Name the header ranges. i.e. I defined a range named "rngPA" on cell A1
  • Adjust your code to pick up intersects

Try defining that name first. Then make sure you have the Immediate window showing and run this code:
Code:
irow = ActiveCell.Row
Debug.Print Intersect(rows(iRow).Entirerow,Range("rngPA").EntireColumn).Address
Debug.Print Intersect(rows(iRow).Entirerow,Range("rngPA").EntireColumn).Value

I think you'll get the idea. :)
 

GaryA

New member
Joined
Apr 6, 2012
Messages
51
Reaction score
0
Points
0
Thanks Ken, worked like a charm!

Updated file attached.
 

Attachments

  • ColRef2.zip
    22.6 KB · Views: 12
Top