Runtime formula error 1004 swaformula

Question

I am getting a formula error message: Run-time error 1004, for the second recurrence of a formula in the code; the code section in question as follows, for quick reference I pasted the two formulas just below, the first one runs OK, the second one gives the error that I cannot figure out.

ActiveWorkbook.Names.Add Name:="pivotsourceFGPO", RefersToR1C1:=swaFormula (no errors when running the macro)

 ActiveWorkbook.Names.Add Name:="pivotsourceorderbase", RefersToR1C1:=swaFormula (FORMULA giving error)

Code: (partial)

swaFormula = "=OFFSET(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & "C" & ActiveCell.Column & ",0,0,COUNTA(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!C" & ActiveCell.Column & ") -" & swarow & ",COUNTA(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & ")-" & swacol & ")"

ActiveWorkbook.Names.Add Name:="pivotsourceFGPO", RefersToR1C1:=swaFormula
Dim PTCache As PivotCache
Dim pt As PivotTable
Dim WS As Worksheet
Dim pvtfld As PivotField

' define the range which serves as the data range

Set WS = Sheets.Add ' add new sheet to hold the pivot table
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,  SourceData:="=pivotsourceFGPO")

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=WS.Range("A1"), TableName:="FGPOPivot")

ActiveWorkbook.ShowPivotTableFieldList = True

pt.PivotFields("Key").Orientation = xlRowField
pt.PivotFields("Key").Position = 1
pt.PivotFields("MTL").Orientation = xlRowField
pt.PivotFields("MTL").Position = 2
pt.PivotFields("Size Code").Orientation = xlRowField
pt.PivotFields("Size Code").Position = 3
pt.PivotFields("Week").Orientation = xlColumnField
pt.PivotFields("Week").Position = 1
pt.AddDataField pt.PivotFields("Wip Qty"), "Sum of Wip Qty", xlSum

'Order base 1 pivot
Sheets("Order base (1)").Select
Range("A1").Select
swarow = ActiveCell.Row - 1
swacol = ActiveCell.Column - 1

' make it comma and not colon
swaFormula = "=OFFSET(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & "C" & ActiveCell.Column & ",0,0,COUNTA(" & _

ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!C" & ActiveCell.Column & ") -" & swarow & ",COUNTA(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & ")-" & swacol & ")"

ActiveWorkbook.Names.Add Name:="pivotsourceorderbase", RefersToR1C1:=swaFormula (FORMULA giving error)

Answer 1

You are relying on the ActiveCell property. There are better ways¹ to reference cells than that but if those 'best practises can be disregarded for a minute, the ActiveCell knows what its .Parent worksheet and workbook are.

Additionally, with no actual worksheet names provided, there is no guarantee that the ActiveSheet's .Name property does not contain a space and may require ticks (e.g. ') to wrap it. Either those should be added as a matter of course or the Range.Address property should be used to generate the strings for the cell range(s).

The non-volatile INDEX function could concevably replace the volatile² OFFSET function but I don't know enough about the layout of your data to suggest an infallible substitute formula.

Dim swaFormula As String, rngName As String
Dim swaRow As Long, swaCol As Long

rngName = "pivotSourceOrderBase"
swaRow = 0: swaCol = 0

With ActiveCell
    swaFormula = "=OFFSET(" & _
                    .Cells(1).Address(external:=True, ReferenceStyle:=xlR1C1) & _
                    ", 0, 0, COUNTA(" & _
                    .Cells(1).EntireColumn.Address(external:=True, ReferenceStyle:=xlR1C1) & _
                    ")-" & swaRow & ", COUNTA(" & _
                    .Cells(1).EntireRow.Address(external:=True, ReferenceStyle:=xlR1C1) & _
                    ")-" & swaCol & ")"
    Debug.Print swaFormula  '<~~ check the formula in the Immediate window
    On Error Resume Next    'may be needed for the next line
    .Parent.Parent.Names(rngName).Delete
    On Error GoTo 0
    .Parent.Names.Add Name:=rngName, RefersToR1C1:=swaFormula
End With

Walk through this and check the formula in the VBE's Immediate window before attempting to apply it to the named range creation.

---

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

² _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.}