☰

Cómo crear un rango con nombre en VBA con fórmula (error de tiempo de ejecución 438)

tag-icon • tag-icon microsoft-excel vba named-ranges
Cómo crear un rango con nombre en VBA con fórmula (error de tiempo de ejecución 438)

Estoy intentando crear un rango con nombre usando VBA. Ambos FirstBodyCelly ColumnCodeson células individuales. Quiero que el rango nombrado eventualmente se defina como algo similar a esto (dependiendo de en qué columna se FirstBodyCellencuentre):

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

Usar el sub siguiente aumenta

Error de tiempo de ejecución '438': el objeto no admite esta propiedad o método

y apunta a la línea con .Name = ColumnCode.Value. ¿Alguna idea de qué está mal con este código?

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

Respuesta1

Workbook.Nameses una propiedad que produce una Namesclase de colección, que expone miembros como Add, Count, Parenty Item.

Un Namesobjeto no tiene una Namepropiedad o un RefersToR1C1miembro (de ahí el error 438: el objeto no admite/expone el miembro/propiedad).

Desea invocar al Addmiembro de ese objeto; esa función toma como parámetros los valores que está intentando asignar allí. Además, .Addressdevolverá una dirección que no esté en formato R1C1. Intenta usar RefersToen lugar de 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

NotaScott Cranerseñala que probablemente querrás usar los .Addressobjetos Rangeque estás proporcionando allí, por lo que el valor real RefersToR1C1sería:

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

información relacionada