☰

Как создать именованный диапазон в VBA с помощью формулы (ошибка выполнения 438)

tag-icon • tag-icon microsoft-excel vba named-ranges
Как создать именованный диапазон в VBA с помощью формулы (ошибка выполнения 438)

Я пытаюсь создать именованный диапазон с помощью 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

решение1

Workbook.Names— это свойство, которое создает Namesкласс коллекции, который предоставляет такие члены, как Add, Count, Parent, и Item.

Объект Namesне имеет Nameсвойства или RefersToR1C1члена (отсюда ошибка 438 — член/свойство не поддерживается/не представлено объектом).

Вы хотите вызвать Addчлен этого объекта - эта функция принимает в качестве параметров значения, которые вы пытаетесь там назначить. Также .Addressвернет адрес, который не в формате R1C1. Попробуйте использовать 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)")

Связанный контент