☰

So erstellen Sie einen benannten Bereich in VBA mit einer Formel (Laufzeitfehler 438)

tag-icon • tag-icon microsoft-excel vba named-ranges
So erstellen Sie einen benannten Bereich in VBA mit einer Formel (Laufzeitfehler 438)

Ich versuche, mit VBA einen benannten Bereich zu erstellen. Sowohl FirstBodyCellals auch ColumnCodesind einzelne Zellen. Ich möchte, dass der benannte Bereich letztendlich so definiert wird, dass er so aussieht (je nachdem, FirstBodyCellin welcher Spalte sich das befindet):

=Offset(Sheet1!$E$8,0,0,COUNTA(Sheet1!$E:$E)-COUNTA(Sheet1!$E$1:$E$7),1)

Die Verwendung des folgenden Subs erhöht

Laufzeitfehler '438': Objekt unterstützt diese Eigenschaft oder Methode nicht

und zeigt auf die Zeile mit .Name = ColumnCode.Value. Irgendeine Idee, was mit diesem Code nicht stimmt?

Sub CreateNamedRange(FirstBodyCell As Range, ColumnCode As Range)
    With ActiveWorkbook.Names
            .Name = "col_" & ColumnCode.Value
            .RefersToR1C1 = "=Offset(" & FirstBodyCell & ",0,0," & _
                          "COUNTA(" & Columns(FirstBodyCell.Column) & ")-COUNTA(" & _
                          Range(Cells(1, FirstBodyCell.Column), FirstBodyCell.Offset(-1, 0)) & _
                          "),1)"
    End With
End Sub

Antwort1

Workbook.Namesist eine Eigenschaft, die eine NamesSammlungsklasse ergibt, die Mitglieder wie Add, Count, Parent, und verfügbar macht Item.

Einem NamesObjekt fehlt eine NameEigenschaft oder ein RefersToR1C1Mitglied (daher Fehler 438 – Mitglied/Eigenschaft wird vom Objekt nicht unterstützt/nicht verfügbar gemacht).

Sie möchten das AddMitglied dieses Objekts aufrufen. Diese Funktion verwendet als Parameter die Werte, die Sie dort zuweisen möchten. Außerdem .Addresswird eine Adresse zurückgegeben, die nicht im R1C1-Format vorliegt. Versuchen Sie es mit RefersToanstelle von RefersToR1C1.

With ActiveWorkbook.Names.Add( _
    Name:="col_" & ColumnCode.Value, _
    RefersTo:="=Offset(" & FirstBodyCell & ",0,0," & _
                      "COUNTA(" & Columns(FirstBodyCell.Column) & ")-COUNTA(" & _
                      Range(Cells(1, FirstBodyCell.Column), FirstBodyCell.Offset(-1, 0)) & _
                      "),1)")
    'the With block variable is the Name object that was created+added to the Names collection:
    Debug.Print .Name
End With

NotizScott Craner.Addressweist darauf hin, dass Sie wahrscheinlich die der Objekte verwenden möchten, die RangeSie dort angeben. Der tatsächliche Wert für RefersToR1C1wäre also:

  RefersTo:="=Offset(" & FirstBodyCell & ",0,0," & _
                    "COUNTA(" & Columns(FirstBodyCell.Column).Address & ")-COUNTA(" & _
                    Range(Cells(1, FirstBodyCell.Column), FirstBodyCell.Offset(-1, 0)).Address & _
                    "),1)")

verwandte Informationen