For Each Cell In Selection Name Manager

I created a macro that takes the active cell and creates a named range (dynamic). It works fine, but I wanted to extend it to allow for the selection of multiple cells.

I added a For loop to search through each cell in the user's selection, but it only seems to take the first cell in any given selection and give it the named range value.

Please Login or Register  to view this content.

I removed the End If thinking that it was ending the For prematurely, but when I did that an error popped up: Compile Error: Next without For.
This doesn't make much sense to me since the For is still indeed there. If anything I should get an If without End If error, but I've seen code before without an End If before.

In any case, is there some syntax I'm missing?

I've also tried searching around to find out how to find the current table was referenced. Currently this code only works on my table called DVTABLE. If I try to use it on another table then it will obviously be referencing the wrong table (and if that header doesn't exist, it will return an error). So I'd like to replace any spot in the formula that says DVTABLE with ActiveCell.Table.Value (or something to that effect).

One last thing: I think I should check to see if that named range already exists (ActiveCell.Value) because if I don't it will simply overwrite it. I'm not sure how to cross-check the ActiveCell.Value with the named range database stored in my workbook though.

Thanks!

Hello Phoenix5794 ,

It would help to see an example or two with before and after results. A dynamic range when written as a formula will expand or contract as items are added to or deleted from the range. In VBA there is no analog to this function. A Named Rrange in VBA is static. It is simply a Name Object that refers to a defined range. To Change what a Named Range refers to, it must first be deleted from the Workbook's Names collection and then Added again as a new Name.

I suppose that's my first issue then:
My selection isn't exactly a named range. It's simply a selection of my cursor across several cells. With that being the case, is there anyway to search through a user's selection of several cells (rather than a pre-defined range)?

Here's my example:
I select cells A5:G5 with my cursors.

Please Login or Register  to view this content.

Should I change my Dim cell to Dim cell As Selection (I've never heard of such a thing)?

As far as named ranges being static, I can understand that. The thing is this macro will change that range if it already exists. Excel has no built in feature to prevent a macro from editing a currently existing named range (which I'm ok with), I'd just like to be able to have an If statement say:

Please Login or Register  to view this content.

I'd like to exemplify as much of this as possible without uploading a book as there's a lot to remove in order to make it legal. I'm hoping that my explanations are enough to go by and thank you for the time to at least attempting to understand them.

Hello Phoenix5794 ,

When iterating through an array or collection with the For Each Next statement, each individual element that makes up the array or collection will be accessed. The order of access is from top left element to bottom right. This is very useful feature when is a Range is non-contiguous.

Please Login or Register  to view this content.

In the example above, the order of the addresses will be as follows:
A1,B1
A2,B2
A3,B3
A4,B4
A5,B5
G10
G11
G12
G13
G14
G15

This is much easier than having to iterate through the same range using the Areas property.

Please Login or Register  to view this content.

I gotcha. And that makes sense, but my range is non-contiguous: a selected group of cells by clicking and dragging across can only be contiguous (unless of course the ctrl key is used in the process).

What you're accommodating for is a range that is pre-defined (as in I would already know what my range is), but I'd like to search through my range given the user's selection by means of using the cursor (click a cell, drag across an array of cells, and un-click to select those cells - which I believe the syntax would be ActiveCell.Selection, but it doesn't work).

SelectedRange.png - here I have manually selected a range of cells and I'd like to go through each cell in that selection to give it a named range (of course this will be geared towards me only selecting the Headers rather than the items within the table).

Is it possible to base my range with my for loops given an arbitrary selection of cells?

Thanks.

Hello Phoenix5794,

This macro will use the first row of the Selected cells as the name of the Name of the Range. The cells below are what the reference will return. This will replace any existing range with your cell selections.

Please Login or Register  to view this content.

Thanks for the reply Leith.
I think I've given you too much information and only created a more enigmatic question (and somehow managed to still not give you enough information - seems I'm bad at asking questions).

I've implemented your code to be used with mine:

Please Login or Register  to view this content.

I needed these to be dynamic ranges in that they're used for data validation lists. Yours will allow for all items to be accounted for, but in a list there would be a long list of empty spaces in the drop-down.
And I can see why you wrote it the way you did - you allow for the lack of knowledge of the name of the table so it just finds the last item and takes all items above for the ride.

Thanks again for the help.

My only question left is how I can accommodate for the variability in the table name: DVTABLE.
I may take this code somewhere else and I'd like to be able to use it on other tables so I need some way to find out the name of the current table that I'm selecting within.
=OFFSET(DVTABLE[[#Headers],[" & Cell & "]].

There must be some way to say =OFFSET(Cell.TableName[[#Headers],[" & Cell & "]], right?

Hello Phoenix5794,

You're right, I still have no idea what you are trying to do. I feel I have exhausted my contributions to solving your problem. Perhaps, there is someone else here who can correctly interpret what it is you want to do. Best of luck.

Could you help me to decipher your code?

For Each Cell In Rng.Rows(1).Cells

It seems like this is searching for every header within my table.

What I mean when I say selection is this: SelectCells.

Hopefully you're able to view Youtube videos. Let me know if I'm wrong in thinking that this For loop is cycling through every cell in the first row.

Thanks again.