Macro to copy a Relative Address across a range and then to convert those Relative Addresses to Absolute Addresses
Gavin Wehlburg
New Member
Members
Forum Posts: 2
Member Since:
July 27, 2015
July 27, 2015
Offline
I have attached a small demo of an large reporting worksheet and would be grateful for help.
I have needed to update the spreadsheet as the program generating the original input data, in its new version, generates a wider range of useful information, which is input to the reporting worksheet.
I am trying to reduce the editing and relinking work - the attached Worksheet contains the demo and troubleshooting Macro's.
Process Overview:
We import the data, tidy it up and when in the correct structure, copy it into the respective entry point Tab in the reporting worksheet. This then feeds the reporting template. For each section we have a respective Tab of Reporting.
In reconstructing, I am linking the reporting section Relative Address, copying these for "Probability and Impact" across a range of 5 further sequential columns, linking them to their data "Relatively". I then convert the addresses to absolute.
The Macro has been structured to automate this and save key strokes.
Example:
The example is attached in its clean format with 3 Prob / Impact sets of Rows where the first column is linked Relatively
A relative duplicate is placed below to use in copying back across the cells to restore original state and blank cells copied to clean up - the process can recommence again for testing
The Macro was split into its two component macro parts to assist in trouble shooting.
Copying the Relative Cell Address across the range from the start point in both “original position” where the Macro was created and in subsequent positions works fine. = Cntrl+r
The second component of then converting the Relative to Absolute, results in the Cells receiving the first range of Absolute addressing = Cntrl+t - does not work. When this Macro runs it converts all to Absolute but it gives each cell the Absolute addresses that were used in the first instance when creating the Macro?? and not those that were created correctly from Cntrl+r and relative to the Address inserted and correctly copied across the range. So it does not start from a new position and run its set of commands correctly.
At this stage I do not know VBA and have used the Macro Recorder to do this.
I would be grateful for assistance in resolving and consolidating into one single Macro
Kind regards
Gavin
SunnyKow
Puchong, Malaysia
VIP
Members
Trusted Members
Trusted Members
Forum Posts: 1432
Member Since:
June 25, 2016
June 25, 2016
Offline
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Blanka Blair, Riny van Eekelen, Srikanthgupta Pb, Simon Lane, Ross Denney
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
1 Guest(s)
Top Posters:
Catalin Bombea: 1909
SunnyKow: 1432
Anders Sehlstedt: 899
Purfleet: 414
Frans Visser: 346
David_Ng: 306
Hans Hallebeek: 279
lea cohen: 245
Jessica Stewart: 219
A.Maurizio: 213
Newest Members:
Francine Walshe
Sean Ladlow
Jorge Chicas
mahmoud salem
Irvine Choolo
Deon Bouwer
Amgad Squires
jim bo
Linda Bock
Hildebrando Rodrigues
Forum Stats:
Groups: 3
Forums: 24
Topics: 6834
Posts: 29981
Member Stats:
Guest Posters: 49
Members: 33567
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen
© Simple:Press —