Som van dynamisch naambereik

[Cobbe]

Waarom werkt dat niet?

In bestand staat de uitleg want moeilijk onder woorden te brengen.

 

[gast0660]

Hoi Cobbe,
In uw bijlage stond uw dynamisch naambereik 'nieuw' ingesteld met een paar (lege) cellen uit Col E, als je die aanpast naar de cellen in Col A werkt het wel.

 

[Hans123]

Ik weet niet hoe je het voor elkaar krijgt dat "nieuw" een andere range krijgt als je de selectie wijzigt (wel aardig overigens) maar als ik de range van nieuw opvraag binnen het selection change event geeft deze $A$1048573:$A$1048576 terwijl deze in benoemde bereiken wel de goede waarden bevat. Mijn gok zou zijn dat het met de toewijzing van "nieuw" ergens niet helemaal goed gaat

 

[Cobbe]

Het naambereik 'nieuw' wordt ingesteld indien je cel A10 selecteert, staat duidelijk in het bestand, dus zou de som = 10 moeten zijn.
Het bereik van naam'nieuw' wijzigt steeds bij selectiewissel en geldt voor de 4 cellen boven de actieve cel.

 

[Cobbe]

Haha nu is naambereik niet meer dynamisch!
Bedankt maar niet wat gewenst.

 

[wieter]

Ja Cobbe, heb ondertussen bericht verwijderd.
Ik had de vraag niet juist begrepen.

 

[VenA]

Zoals door Hans123 al opgemerkt wordt het adres $A$1048573:$A$1048576. Ga in een willekeurige cel staan en draai dit macrotje eens

Sub VenA()
  MsgBox Blad1.Range("nieuw").Address & vbLf & ActiveWorkbook.Names("nieuw").RefersToR1C1
End Sub

 

[Cobbe]

De vraag blijft natuurlijk waarom dat gebeurt.
Als ik A10 selecteer en klik in het naamvak wordt A6:A9 geselecteerd. Klopt want dat is het naambereik 'nieuw'

Waarom wijzigt VBA dat dan op eigen houtje?

 

[Haije]

Cobbe,

als ik je bestand open is bereik "nieuw" gedefinieerd als $A$1048573:$A$1048576, de vier "voorgaande" cellen van A1
Ik ben zeer benieuwd naar hoe je "nieuw" als dynamisch bereik benoemd hebt, want uit de beschikbare code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
 Cells(1, 5) = WorksheetFunction.Sum(Range("oud"))
 Cells(1, 6) = WorksheetFunction.Sum(Range("nieuw"))
End Sub

kan ik dat niet opmaken.

Graag een tipje van de sluier oplichten.....

 

[Hans123]

Het gaat jouw probleem niet oplossen, maar hoe krijg je het voor elkaar op de range "nieuw" dynamisch te maken op basis van de geselecteerd cel?

 

[Cobbe]

Maar heel simpel via Naam definiëren als Blad1!A4:A1

Als je nu E10 selecteert verwijst nieuw naar E6:E9

 

[Cobbe]

Weet nog altijd niemand waarom dat naambereik steeds aangepast wordt?
Want daarmee kan ik gelijk waar in een gegevenstabel gaan staan en krijg ik het resultaat van de (nu)4cellen daarboven.

 

[rebmog]

Het lijkt erop dat in VBA blijkbaar standaard cel A1 als basis wordt gezien en niet de actieve cel. Het is overigens logisch dat in E9 altijd een nul staat, want het bereik is immers dynamisch. Wellicht dat een kleine workaround kan helpen. Voeg hiervoor een extra blad toe aan je workbook:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Set rng = Sheets(2).Range(Target.Address)
    rng.Formula = "=SUM(nieuw)"
    Cells(1, 5) = WorksheetFunction.Sum(Range("oud"))
    Cells(1, 6) = rng
    rng.ClearContents
End Sub

 

[rebmog]

Ik heb de code nog even in het bestand gezet, zodat duidelijker is hoe het werkt. In cel F1 wordt telkens met de macro de sommatie van de 4 rijen boven de actieve cel neergezet. In cellen E21 t/m G21 kun je zien hoe de sommatie met een dynamisch bereik werkt. Dat dit bereik ogenschijnlijk steeds wijzigt komt omdat bij openen van namenbeheer telkens het bereik wordt getoond dat op dat moment van toepassing is. En dat is dus afhankelijk van de actieve cel en zal daarom per definitie anders moeten zijn bij een andere actieve cel.

 

[snb]

Het doet me sterk denken aan de eigenschap 'RelativeTo ' van de eigenschap 'address"

Range.Address Property
Returns a String value that represents the range reference in the language of the macro.
Syntax

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

expression A variable that represents a Range object.

Parameters

Name Required/Optional Data Type Description
RowAbsolute Optional Variant True to return the row part of the reference as an absolute reference. The default value is True.
ColumnAbsolute Optional Variant True to return the column part of the reference as an absolute reference. The default value is True.
ReferenceStyle Optional XlReferenceStyle The reference style. The default value is xlA1.
External Optional Variant True to return an external reference. False to return a local reference. The default value is False.
RelativeTo Optional Variant If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. This argument is a Range object that defines the starting point.

 

[Cobbe]

@Rebmog
Bedankt voor al de moeite maar mbv VBA- kan ik het ook oplossen(uw voorstel werkt wel) maar het moet toch mogelijk zijn om dit via naambereiken op te lossen.

@snb
Ook bedankt voor het onder de loep te nemen van mijn probleem maar kan je dat ook in het schoon Vlaamsch uitleggen wat je daarmee bedoelt?

 

[VenA]

Als je ipv de naam het bereik opneemt waar de naam naar verwijst dan gaat het wel goed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Cells(1, 5) = WorksheetFunction.Sum(Range("oud"))
  Cells(1, 6).Formula = "=sum(" & Replace(ActiveWorkbook.Names("nieuw").RefersTo, "=", "") & ")"
End Sub

Waarom het niet op de 'normale' manier gaat staat waarschijnlijk in het bericht van snb, maar daar kreeg ik het niet mee aan de praat. Wat je er verder mee wil, is natuurlijk weer een geheel andere vraag.

 

[Cobbe]

Ook jij bedankt natuurlijk voor het nadenken maar is weer VBA en dat zou ik dus liever niet gebruiken in dit specifieke geval.
Met een dynamische naamdefinitie kan ik snel omwisselen naar kolomgedeelte of rijgedeelte om een uitkomst van een berekening te bekomen.

 

[HSV]

Doordat de naam 'nieuw' relatief is gemaakt veranderd het steeds.
Dit is ook een oplossing lijkt me.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
Range(Target.Offset(-4), Target.Address).Name = "nieuw"
 Cells(1, 5) = WorksheetFunction.Sum(Range("oud"))
 Cells(1, 6) = WorksheetFunction.Sum(Range("nieuw"))
End Sub

 

[Cobbe]

Ook jij bedankt maar het blijft VBA, dat is nu net het probleem.

Als ik in A10 sta en ga dan naar Naamvak en kies voor nieuw wordt A6:A9 geselecteerd, dat klopt toch
hetzelfde wanneer ik in bv E20 sta doet hij dat ook goed maar neemt dit bereik niet mee in een formule.