数式を使用して VBA で名前付き範囲を作成する方法 (実行時エラー 438)

Question

VBA を使用して名前付き範囲を作成しようとしています。 と はどちらもFirstBodyCell単一ColumnCodeのセルです。名前付き範囲は、最終的に次のように定義される必要があります ( がどの列FirstBodyCellにあるかによって異なります)。

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

以下のサブを使用すると

実行時エラー '438': オブジェクトはこのプロパティまたはメソッドをサポートしていません

と の行を指します.Name = ColumnCode.Value。このコードのどこが間違っているのか、何か考えはありますか?

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

Answer 1

Workbook.Namesは、、、、などのNamesメンバーを公開するコレクション クラスを生成するプロパティです。AddCountParentItem

オブジェクトにはプロパティまたはメンバーNamesがありません(したがって、エラー 438 - メンバー/プロパティはオブジェクトによってサポート/公開されていません)。NameRefersToR1C1

そのオブジェクトのメンバーを呼び出すAddと、その関数は、そこに割り当てようとしている値をパラメータとして受け取ります。また、 は.AddressR1C1 形式ではないアドレスを返します。RefersToの代わりにを使用してください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

注記スコット・クレイナー.Addressは、おそらくそこに提供しているオブジェクトのを使用することになるだろうと指摘しているのでRange、 の実際の値はRefersToR1C1次のようになります。

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