automatically updating range names??

[AndyKoller]

Hi everyone, i tried to find a solution for following situation:

I have a range of 9 cells located in A2 to A10. The name of this range should be whatever the text in cell A1 is.

The Problem is that the content of cell A1 can change since it is reflecting the content of another cell on a different sheet.

My probelm is that once i define the range to be named according A1 it will keep that name, even if the content of A1 changes.

How can i program in VBA that the name of the cells in range A2 to A10 always is whatever isthe text in A1 is? If Aq changes the name of the cells in range A2 to A10 should change as well; Plus the old name should be deleted.
I think it might work with some event trigger and then a automatic naming of a range. I tried a few things but nothing really worked. (not very exeprianced vit VBA)

Any help is welcome.

Andy

 

[VoG]

The Worksheet_Calculate event is triggered every time the sheet calculates. Because A1 contains a formula referring to D5, changing the value of D5 causes the sheet to calculate and the code to fire.

 

[AndyKoller]

Thanks a 1000 Peter,
i tried the code in my "real" sheet which has a bunch of data validation (drop down lists) refereing to ranges and drop-downs depending on other drop downs via the

=INDIRECT(SUBSTITUTE([cell1]," ","_"))

code.

everythings works perfect.


Thank you again.

 

[AndyKoller]

Actually, i have another question. My real sheet has dozends of such ranges. They all look the same: always the name above 9 data cells.

They might be located anywhere on the sheet.

Example
Range name is in D12 and the values are in D13:D21
Name is in Z119 and its values are in Z120:D129
etc.

I could copy you code dozends of times to catch all the ranges. I wonder if you know a code which searches for 10 Cells above eachoter and then automatically assings the top cell as the name of the 9 cells below it. On the whole sheet no matter how many such ranges are on it.

The criterias is always 10 cells. The top is the name the 9 below are the range. If there are only 7 cells above the code should do nothing to them.

Andy

 

[VoG]

Try (only slightly tested).

Private Sub Worksheet_Calculate()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
    If InStr(nm.RefersTo, Me.Name) <> 0 Then
        With Range(nm.RefersTo)
            .Name.Delete
            .Name = .Resize(1).Offset(-1).Value
        End With
    End If
Next nm
End Sub

 

[AndyKoller]

it gives me an runtime error 1004 Method 'Range of Object_worksheet' failed.

and if i click on debug it jumps to

With Range(nm.RefersTo)

I maybe failed to inform you that i do not have all the ranges in the sheet already defined.

The code i am looking for also would find these ranges first on the sheet and then name them.

The ranges should be:
found,
defined,
named,
and updated whenever the top cell over the 9-cell range changes name


The code pretty much should find blocks of 10 cells in a row and defin the 9 lower cells as range and the top cell should be the name of this range.

The ranges can be next to each other in neighbor colums. Example:

There might be a range at E4:E12 (its name is in E3)
and
another such range can be found directly in colum F at F8:F16 (its name is in F7.


This code is waaaaaayyyyy over my head but it would save us from defining dozends, maybe 100s of ranges on a sheet manually.

Andy

 

[VoG]

Sorry but it works for me.

I have absolutely no idea how to apply this (in general) to a range without a defined range name

 

[AndyKoller]

Somewhere i had a piece of VBA code once that automatically found cells that fulfill a certain critera in a sheet.
Example: ranges of cells that fullfill a certain criteria like "exactly 10 cells neighboring each other that are not empty"

I have to dig in my files and try to remember which seeht that was. If i find it i will post it here as a start point.

Once we are able to find these ranges on a sheet you might be able to help me and name them accoring the content in the top cell.


Thanks for now