Namen dynamisch definieren

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Markus,
ein Ansatz, wenn fortlaufend die Buchtabenbezeichnungen auftauchen:

	A	B	C
1	AM	1	2
2	AM	2	5
3	AM	3
4	CG	4
5	CG	5
6	CG	6

Formeln der Tabelle

Zelle	Formel
C1	=MITTELWERT(AM)
C2	=MITTELWERT(CG)

Namen in Formeln

Zelle	Name	Bezieht sich auf
C1	AM	=INDEX(Tabelle1!$B:$B;MIN(WENN(Tabelle1!$A$1:$A$1000="AM";ZEILE(Tabelle1!$1:$1000)))):INDEX(Tabelle1!$B:$B;MAX(WENN(Tabelle1!$A$1:$A$1000="AM";ZEILE(Tabelle1!$1:$1000))))
C2	CG	=INDEX(Tabelle1!$B:$B;MIN(WENN(Tabelle1!$A$1:$A$1000="CG";ZEILE(Tabelle1!$1:$1000)))):INDEX(Tabelle1!$B:$B;MAX(WENN(Tabelle1!$A$1:$A$1000="CG";ZEILE(Tabelle1!$1:$1000))))

Namen verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
MfG Christian

Hi Markus,
auf die schnelle könnte ich das so lösen. Hat bestimmt noch Verbesserungspotential.
Wenn du in deiner Mustertabelle das Makro "MakeAllNames" aufrufst, passiert folgendes:
es wird ein Name "Name_AM" erstellt, der den Bereich B1:B3 umfasst.
es wird ein Name "Name_CG" erstellt, der den Bereich B4:B6 umfasst.
änderst du jetzt die Mustertabelle folgendermaßen:
A B
AM 1
AM 2
AM 3
AM 4
CG 5
CG 6
XY 7
und rufst das Makro erneut auf, dann passiert folgendes:
der vorhanden Name "Name_AM" wird auf den Bereich B1:B4 umgeschrieben
der vorhanden Name "Name_CG" wird auf den Bereich B5:B6 umgeschrieben
es wird ein Name "Name_XY" erstellt, der den Bereich B7 umfasst.
Hier das Makro:
Option Explicit Const ErsteZeile As Long = 1 Const SpalteSuchString As Long = 1 Const SpalteErgebnisse As Long = 2 Const TabelleSuchen As String = "Sheet1" Sub MakeAllNames() Dim LastRow As Long Dim i As Range 'Geht jeden Wert in der Suchspalte durch, 'erstellt oder aktualisiert bei jedem ersten Vorkommen den dazugehörigen Namen With Sheets(TabelleSuchen) LastRow = .Cells(.Rows.Count, SpalteSuchString).End(xlUp).Row For Each i In .Range(.Cells(ErsteZeile, SpalteSuchString), .Cells(LastRow, SpalteSuchString) _ ) If WorksheetFunction.CountIf(.Range(.Cells(ErsteZeile, SpalteSuchString), .Cells(i.Row, _ SpalteSuchString)), i.Value) = 1 Then Call ReferNameNew(i.Value) End If Next i End With End Sub Sub ReferNameNew(SuchString As String) Dim ZeileVon As Long Dim ZeileBis As Long Dim r As Range With Sheets(TabelleSuchen) 'Zeilen von-bis ermitteln, in Range übergeben ZeileVon = WorksheetFunction.Match(SuchString, .Cells(1, SpalteSuchString).EntireColumn, _ False) ZeileBis = -1 + ZeileVon + WorksheetFunction.CountIf(.Cells(1, SpalteSuchString). _ EntireColumn, SuchString) Set r = .Range(.Cells(ZeileVon, SpalteErgebnisse), .Cells(ZeileBis, SpalteErgebnisse)) End With 'vorhandenen Namen erweitern. Falls Name nicht vorhanden, neuen Namen anlegen. On Error GoTo hell ActiveWorkbook.Names("Name_" & SuchString).RefersToR1C1 = "=" & TabelleSuchen & "!" & r.Address( _ ReferenceStyle:=xlR1C1) On Error GoTo 0 GoTo heaven hell: ActiveWorkbook.Names.Add Name:="Name_" & SuchString, RefersToR1C1:="=" & TabelleSuchen & "!" & _ r.Address(ReferenceStyle:=xlR1C1) heaven: End Sub Achtung! Ich gehe davon aus, dass die Tabelle sortiert ist wie im Beispiel! Solltest du eine solche Tabelle haben:
A B
AM 1
AM 2
AM 3
CG 5
AM 4
CG 6
wird das Makro die Namen "irgendwie" definieren, aber nicht korrekt.
Die Liste wird im Hintergrund aktualisiert, so das sich die Liste immer wieder verändert.
Entweder, du rufst bei jeder Änderung im Hintergrund von Hand das Makro MakeAllNames auf, oder du schreibst einen Call der es automatisch macht - wo das Sinn macht musst du entscheiden. Ich sag mal, immer beim öffnen der Tabelle sollte reichen? Dann Callst du das Makro aus "Workbook_Open".
Grüße,
Klaus M.vdT.

Beliebteste Forumthreads (12 Monate)

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread