Macro to name ranges based on the value of two other cells

[Scott26]

Hi,

I know it should be simple enough, and I have seen many similar Q&As on this sort of topic. But I couldn't find one that solved it for me, and I couldn't work it out by myself. VBA is not my strength (yet).

I want to name hundreds of ranges. I don't want to type the name myself. Ideally I select the top row of a range and run the macro. It then names the range based on 2 separate cell values: OFFSET(-1,0) and OFFSET(0,-2) from the top cell in the range. The locations of these cells can vary.

The ranges are all on the same sheet.

Do I need to provide more info? Or is this sufficient?

Thanks in advance.

 

[Jeffrey Lopez]

So here is the thing with what you are trying to do... you can name the ranges pretty easily, so for example:

ActiveWorkbook.Names.Add Name:=ActiveCell.Offset(-1, 0).Value & ActiveCell.Offset(0, -2).Value, RefersTo:=Range("A1:A15")

The problem you are going to run into is you need to be able to tell it what the range is for each cell...

Now you could use a variable there, but still need to define what each range is...

I hope that make sense!

 

[Scott Huish]

Why do you need to name so many ranges?

 

[Scott26]

I need to name the ranges to be used in a bunch of formulae. There are a lot of small data sets that get used, and it's a nightmare to track them using A1-references.

In order to tell it what the range is, I figured I'd highlight the range, then hit the macro shortcut. I haven't checked Jeffrey's VBA yet, but I presume the last bit: RefersTo:=Range("A1:A15") is indicating the range. So i'd need to change this bit. If anyone wants to save me fiddling with it, you're more than welcome.

 

[Jeffrey Lopez]

but I presume the last bit: RefersTo:=Range("A1:A15") is indicating the range.

Indeed it is. Now you can use Offsets and a few other tricks if the references are static, i.e.

For i = 1 To 100
ActiveWorkbook.Names.Add Name:=ActiveCell.Offset(-1, 0).Value & ActiveCell.Offset(0, -2).Value, RefersTo:=Range(Cells(i, 1), Cells(i, 3))
Next i

Which would return values of A1:C1, A2:C2, A3:C3 and so on... which would loop 100 times and give the named range to each one of those ranges...

Basically if you just give a little more info, maybe a sceenshot of your data set, we might be able to narrow it down to better help you.

 

[Scott26]

Sorry about the delays. I've not really had time to work on this stuff lately.

https://www.dropbox.com/s/643k61k5dic7jdu/demo.xlsx

That *should* link to a a demo sheet. In that example, there are a lot of things to be named.

Range E3:E40 is named "Team01GoalsScored" (which could be C1&E2). Range L3:L40 is named "Team02GoalsScored" (which could be derived from J1&L2). This pattern carries across to EH3:EH40, which I want to be named "Team20GoalsScored" (which could be derived from EF1&EH2).

Similarly, the column to the right of each of these Team01GoalsConceded, Team02GoalsConceded etc etc.

In rows 42-44 there are many individual cells I want to name. C43 is named Team01LastGame (held in B43). J43 is named Team02LastGame (held in I43). Similar story for the 2 cells beneath this, and the Team01NextGame over in E43.

Next chunk down, every data set gets a name. Team01HomeGoals, Team01HomeGoalsScored, Team01HomeGoalsConceded, Team01HomePoints. In addition, the top 6 cells (E48:E53) is to be called Team01HomeGoals6Games. Similar story for all the away points.

The loop could be used i'm sure, as there are usually 20 variants for each naming. Sometimes 38, but not in this instance.