Alan Downie
New Member
Good day to everyone. This is my first time ever on a forum so please excuse me if I get the etiquette wrong. I have read and will try to follow the rules. I am also legally blind so sometimes miss small details. I have taken several courses thru Chandoo.org but unfortunately VBA was not one of them. I am using Office 2013 (office 365) on a windows 8.1 platform.
I really could use some help to create a code that will change cell references in formulae in a given range into range names. I have uploaded a sample file.
I wish to be able to select a variable range, for example E2:V8 and give each cell in the range a unique name. The name is to be based on the Name found in row 9 of each column. For instance E9 is named REV_WK_RM_DISC_TOT and D9 is named REV_WK_RM_INCID_TOT and so on. Rows 2 to 8 refer to days of the Week, Mon, Tue, Wed, etc. What I would like is to name E2 with the base name REV_WK_RM_DISC_TOT but add a suffix of _MON and E3 with the base name REV_WK_RM_DISC_TOT and a suffix _TUE and continue the series for each row and column in the selected range.
I would then like the formulae in row 9 in the selected range to reflect their new names instead of the cell references. I have completed this process manually in Columns
I have completed this process manually in Columns B thru F but it is going to take me a very long time to go through the whole sheet this way.
It is important for me to have flexibility in the Macro because the ranges are variable and the named cell can be in either row 8 or 9 though mostly 9.
I hope I have explained this well enough. I read that to much detail is better than not enough and I am a detailed person.
I do have another question relating to hyperlinks in the same sheet but will post that as a new thread.
I thank everyone in the community in advance for any help you can give me.
Kind Regards and Blessings to all.
Alan
I really could use some help to create a code that will change cell references in formulae in a given range into range names. I have uploaded a sample file.
I wish to be able to select a variable range, for example E2:V8 and give each cell in the range a unique name. The name is to be based on the Name found in row 9 of each column. For instance E9 is named REV_WK_RM_DISC_TOT and D9 is named REV_WK_RM_INCID_TOT and so on. Rows 2 to 8 refer to days of the Week, Mon, Tue, Wed, etc. What I would like is to name E2 with the base name REV_WK_RM_DISC_TOT but add a suffix of _MON and E3 with the base name REV_WK_RM_DISC_TOT and a suffix _TUE and continue the series for each row and column in the selected range.
I would then like the formulae in row 9 in the selected range to reflect their new names instead of the cell references. I have completed this process manually in Columns
I have completed this process manually in Columns B thru F but it is going to take me a very long time to go through the whole sheet this way.
It is important for me to have flexibility in the Macro because the ranges are variable and the named cell can be in either row 8 or 9 though mostly 9.
I hope I have explained this well enough. I read that to much detail is better than not enough and I am a detailed person.
I do have another question relating to hyperlinks in the same sheet but will post that as a new thread.
I thank everyone in the community in advance for any help you can give me.
Kind Regards and Blessings to all.
Alan