VBA: Namensmanager in Z1S1 Schreibweise
23.11.2021 20:29:32
Scotch
bislang bin ich immer als stiller Mitleser mit meinen Problemchen fertig geworden - diesmal leider nicht.
Ich habe eine Tabelle Werten, welche ich erstmal über den Namensmanager ordnen möchte (viele verschieden große Bereiche). Die Namen möchte ich dann in Formeln weiternutzen. Beispielsweise versehe ich einen Bereich mit dem Namen
s_1_4_1_1000_d_m
=MITTELWERT(s_1_4_1_1000_d_m)
Leider erhalte ich nach Durchlauf des Makros im Namensmanager die gewünschten Namen, jedoch beziehen diese sich auf Zellen in der Z1S1 Schreibweise und die Formeln mit den Namen sind nicht nutzbar. Im Namensmanager wird mir als Wert eine leere Klammer {...} angezeigt.
Wenn ich dieselben Bereiche händisch einpflege, ist der Bezug in A1 Schreibweise und die Formeln funktionieren.
Erst habe ich mit RefersTo: Range(Cells(), Cells()) gearbeitet, bin dann auf einen String mit Zellennamen umgestiegen. Das Problem bleibt. Auch das umstellen von Excel auf die Z1S1 Bezugsart hat nicht funktioniert...
Folgender Codeschnipsel erzeugt die Namen, bei Bedarf kann ich die Datei gerne uploaden.
Sub Namenvergeben()
For blatt = 3 To 4
For scan = 1 To 100 Step 9
zeilestart = 3
For umo = 1 To 3
If umo = 1 Then
suffix = "u"
ElseIf umo = 2 Then
suffix = "m"
ElseIf umo = 3 Then
suffix = "o"
End If
zeileende = ThisWorkbook.Sheets(blatt).Cells(zeilestart, scan).End(xlDown).Row
Application.CutCopyMode = False
Namebasis = "s_" & Replace(Left(ThisWorkbook.Sheets(blatt).Cells(1, scan).Value, Len(ThisWorkbook.Sheets(blatt).Cells(1, scan).Value) - 4), "-", "_") & "_"
For i = 1 To 1
vergname = Namebasis & LCase(Left(ThisWorkbook.Sheets(blatt).Cells(2, scan + i), 1)) & "_" & suffix
ActiveWorkbook.Names.Add Name:=vergname, RefersTo:="='" & ThisWorkbook.Sheets(blatt).Name & "'!" & "$" & CStr(spaltenname(scan + i)) & "$" & CStr(zeilestart) & ":$" & CStr(spaltenname(scan + i)) & "$" & CStr(zeileende)
ActiveWorkbook.Names(vergname).Comment = ""
Next i
For i = 6 To 7
vergname = Namebasis & Left(ThisWorkbook.Sheets(blatt).Cells(2, scan + i), 1) & suffix
ActiveWorkbook.Names.Add Name:=vergname, RefersTo:="='" & ThisWorkbook.Sheets(blatt).Name & "'!" & "$" & CStr(spaltenname(scan + i)) & "$" & CStr(zeilestart) & ":$" & CStr(spaltenname(scan + i)) & "$" & CStr(zeileende)
ActiveWorkbook.Names(vergname).Comment = ""
Next i
zeilestart = zeileende + 2
Next umo
Next scan
Next blatt
End Sub
Function spaltenname(ByVal spalte As Integer) As String
spaltenname = Left(Replace(Sheets(1).Cells(1, spalte).Address, "$", ""), Len(Replace(Sheets(1).Cells(1, spalte).Address, "$", "")) - 1)
End Function
VG
Scotch