VBA, Names

[Alan Downie]

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

 

[Somendra Misra]

Hi Alan,

Use below code to name range as per your choice:

Sub rename()

For Each Rng In Selection

String1 = Application.WorksheetFunction.Substitute(Cells(1, Rng.Column).Value, Chr(10), "") & "_" & Format(Cells(Rng.Row, 1).Value, "ddd")
Rng.Name = String1
Next

End Sub

1. Select the range of cells and run the macro.

I am still working on replaced range in the formulas on row 9, but I feel it's not required, because every SUM is pointing to cells above it.

Regards,

 

[p45cal]

or:

Sub blah()
For Each cll In Intersect(Selection, Range("E2:V8")).Cells
  cll.Name = Cells(9, cll.Column).Name.Name & Choose(cll.Row - 1, "_MON", "_TUE", "_WED", "_THU", "_FRI", "_SAT", "_SUN")
Next cll
End Sub

 

[p45cal]

re:

I wish to be able to select a variable range, for example E2:V8 and give each cell in the range a unique name.

and:

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.

The following macro requires you to select a single block of cells, 8 rows by as many columns as you want. The last row of the selected range is where the formulae will be. It is a requirement of the macro that all the cells in the last row of the selection have already been given a Name.

I would then like the formulae in row 9 in the selected range to reflect their new names instead of the cell references.

The formula created by the macro is not a translation/substitution of any existing formula, merely a sum of the 7 cells above using the 7 new Names.

The code:

Sub blah3()
Set myrange1 = Selection
If myrange1.Rows.Count <> 8 Or myrange1.Areas.Count > 1 Then
  MsgBox "Not 8 rows in selection or not a contiguous selection"
  Exit Sub
End If
toprow = myrange1.Row
totalrow = myrange1.Rows(myrange1.Rows.Count).Row
Set CellsToGiveNewNamesTo = myrange1.Resize(myrange1.Rows.Count - 1)
For Each colm In CellsToGiveNewNamesTo.Columns
  celformula = "= "
  For Each cll In colm.Cells
    cll.Name = Cells(totalrow, cll.Column).Name.Name & Choose(cll.Row - toprow + 1, "_MON", "_TUE", "_WED", "_THU", "_FRI", "_SAT", "_SUN")
    celformula = celformula & cll.Name.Name & "+"
  Next cll
  colm.Cells(colm.Rows.Count).Offset(1).Formula = Left(celformula, Len(celformula) - 1)
Next colm
End Sub

 

[jeffreyweir]

This is my first time ever on a forum so please excuse me if I get the etiquette wrong.

Dude, you got everything right: a sufficiently detailed explanation, a sample file, a clear, meaningful title, and even a blessing! If regular posters did as good as that, my life would be much easier.

I've been working on some code to create names for large blocks of cells that allows you to add suffixes and prefixes. I'll dig it out, dust it off, and see if it suits.

 

[Alan Downie]

re:and:The following macro requires you to select a single block of cells, 8 rows by as many columns as you want. The last row of the selected range is where the formulae will be. It is a requirement of the macro that all the cells in the last row of the selection have already been given a Name.

The formula created by the macro is not a translation/substitution of any existing formula, merely a sum of the 7 cells above using the 7 new Names.

The code:

Sub blah3()
Set myrange1 = Selection
If myrange1.Rows.Count <> 8 Or myrange1.Areas.Count > 1 Then
  MsgBox "Not 8 rows in selection or not a contiguous selection"
  Exit Sub
End If
toprow = myrange1.Row
totalrow = myrange1.Rows(myrange1.Rows.Count).Row
Set CellsToGiveNewNamesTo = myrange1.Resize(myrange1.Rows.Count - 1)
For Each colm In CellsToGiveNewNamesTo.Columns
  celformula = "= "
  For Each cll In colm.Cells
    cll.Name = Cells(totalrow, cll.Column).Name.Name & Choose(cll.Row - toprow + 1, "_MON", "_TUE", "_WED", "_THU", "_FRI", "_SAT", "_SUN")
    celformula = celformula & cll.Name.Name & "+"
  Next cll
  colm.Cells(colm.Rows.Count).Offset(1).Formula = Left(celformula, Len(celformula) - 1)
Next colm
End Sub

A very big thank you to all those who replied especially p45cal who got it spot on. The macro "sub blah3()" meets my requirements exactly and runs flawlessly. It will save me many, many hours of work.

Once again, thank you and blessings to all. To jeffreyweir, thank you very much for your kind words of encouragement.

As the question was my first post, so this is my first reply. I was concerned that my original post seemed to have been redirected. Can someone advise whether I posted in the correct place? I used the "Ask an Excel Question" button to post.
Also is there anything else I should do to accept p45cal's solution?

Alan

 

[Alan Downie]

A very big thank you to all those who replied especially p45cal who got it spot on. The macro "sub blah3()" meets my requirements exactly and runs flawlessly. It will save me many, many hours of work.

Once again, thank you and blessings to all. To jeffreyweir, thank you very much for your kind words of encouragement.

As the question was my first post, so this is my first reply. I was concerned that my original post seemed to have been redirected. Can someone advise whether I posted in the correct place? I used the "Ask an Excel Question" button to post.
Also is there anything else I should do to accept p45cal's solution?

Alan

Hi everyone
I wonder if I could ask for your valuable time to modify p45cal's above code to accomplish another task? I wish to replace formulae containing cell references in a selected column with their range names along a row. i.e D3 = sum(E3+E4+E5 etc. to D3 = sum(name 1 + name 2 ETC.) The names have already been created

On the same worksheet but probably another topic (I did post as a seperate question but got no reply) a question relating to automatically creating a hyperlink index to each named cell in a selected range with the anchor cells beginning in a selected range of the sheet or on another sheet. Hyperlink destination is the range name address and hyperlink name is the range name.

Thanking you in advance with many blessings
Alan

 

[jeffreyweir]

Hi Alan. You might be able to accomplish this using Excel's native 'Apply Names' feature. In my version of Excel (2013) you access this by clicking on the very small triangle to the right of the Define Name icon in the Formulas tab.

 

[jeffreyweir]

Out of curiosity, why do you actually want to use all those named ranges, instead of just using a simple SUM function in row 9?

 

[Somendra Misra]

@jeffreyweir

The same question I raised on my comment #2.

Regards,

 

[Alan Downie]

Out of curiosity, why do you actually want to use all those named ranges, instead of just using a simple SUM function in row 9?

Hi Jeffrey
The sheet I uploaded is just one of 20 in the workbook. I use the named data in the uploaded data entry sheet in the other sheets and I report to others who often ask how I arrive at a certain result. If they can look in the formula bar and see a formula made up with meaningful names instead of cell references it avoids confusion.

 

[Alan Downie]

@jeffreyweir

The same question I raised on my comment #2.

Regards,

Hi Somendra
The sheet I uploaded is just one of 20 in the workbook. I use the named data in the uploaded data entry sheet in the other sheets and I report to others who often ask how I arrive at a certain result. If they can look in the formula bar and see a formula made up with meaningful names instead of cell references it avoids confusion.

 

[Alan Downie]

Hi Jeffrey
The sheet I uploaded is just one of 20 in the workbook. I use the named data in the uploaded data entry sheet in the other sheets and I report to others who often ask how I arrive at a certain result. If they can look in the formula bar and see a formula made up with meaningful names instead of cell references it avoids confusion.

Hi Jeffrey
Just realized I did not answer your suggestion of using "apply Names". I have tried this but some formulae contain more that a dozed elements albeit only simple sums and locating the specific names to apply from the "apply Names" list can take quite some time. I am really hoping that excel can recognize that for instance cell B10 has a range name and substitute the name for the cell address in the formulae without me having to go through the name selection hassle.

Thank you again and Blessings.

Alan

 

[jeffreyweir]

If they can look in the formula bar and see a formula made up with meaningful names instead of cell references it avoids confusion.

I gotta say Alan that I find this:

=SUM(D2:D8)

...a heck of a lot less confusing that this:

=REV_WK_RM_SALES_MON+REV_WK_RM_SALES_TUE+REV_WK_RM_SALES_WED+REV_WK_RM_SALES_THU+REV_WK_RM_SALES_FRI+REV_WK_RM_SALES_SAT+REV_WK_RM_SALES_SUN

And that simple SUM is a lot easier for someone to audit too.

Giving every cell in a range that you intend to sum a Named Range is overkill.

In fact, if you change your data into an Excel Table (using the Ctrl + T shortcut) and remove the line breaks (carriage returns) that you've inserted using Ctrl +Enter, then you'll see that when you reference the table column in your SUM, you get something like this:
=SUM(Table1[REV_RM_INCID_TOT])

If you rename the table from Table1 into something meaningful, and replace the
header rows with something in plainer English, you'll end up with something like this:
=SUM(Revenue[Incidentals])
...which is much easier on the eye, and instantly understandable. Your current approach actually hinders understanding, instead of fostering it.

 

[Alan Downie]

I gotta say Alan that I find this:

=SUM(D2:D8)

...a heck of a lot less confusing that this:

=REV_WK_RM_SALES_MON+REV_WK_RM_SALES_TUE+REV_WK_RM_SALES_WED+REV_WK_RM_SALES_THU+REV_WK_RM_SALES_FRI+REV_WK_RM_SALES_SAT+REV_WK_RM_SALES_SUN

And that simple SUM is a lot easier for someone to audit too.

Giving every cell in a range that you intend to sum a Named Range is overkill.

In fact, if you change your data into an Excel Table (using the Ctrl + T shortcut) and remove the line breaks (carriage returns) that you've inserted using Ctrl +Enter, then you'll see that when you reference the table column in your SUM, you get something like this:
=SUM(Table1[REV_RM_INCID_TOT])

If you rename the table from Table1 into something meaningful, and replace the
header rows with something in plainer English, you'll end up with something like this:
=SUM(Revenue[Incidentals])
...which is much easier on the eye, and instantly understandable. Your current approach actually hinders understanding, instead of fostering it.

Hi Jeffrey Weir
Thank you for bringing me back to earth. I must have been intoxicated by the power of VBA and did get carried away. My original set up was as you suggest and I started out making enhancements and did not know when to stop.

Once again, blessings and gratitude.

Alan

 

[jeffreyweir]

Happens to the best of us. It's all part of the journey.

This isn't VBA by the way, but just names (and I'm sure that's what you meant)

I actually penned a VBA Serenity Prayer some time back (which ain't bad going for an atheist). It goes:
Lord grant me the VBA skills to automate the things I cannot easily change; the knowledge to leverage fully off the inbuilt features that I can; and the wisdom to know the difference.