R1C1 VBA Assistance PLZ

[Jeff9915]

I've Tried many times to get the systax correct when tring to modify R1C1 selections.

I only need to change the row count in a pivot table for adding new data. The counting is no problem getting it in my code is.

Below is the code I need to sub a variable for R11 in the example. I just cannot get the syntax right to modify it.


Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Combined!R1C1:R11C9"
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False

 

[Richie(UK)]

Hi Jeff,

Welcome to the board.

It should be a simple matter of inserting the variable between two halves of the formula (enclosed within quotes).

Like this:

Sub TestR1C1()
    Dim lRow As Long
    
    'ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[4]C[-1])"
    'becomes
    lRow = 4
    ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[" & lRow & "]C[-1])"
    
End Sub

HTH

 

[Jeff9915]

Thanks for the info but I don't see how this works form my code above? I'm newish to VBA and cannot see the relationship. I require more details. TIA

 

[Jeff9915]

OK tried to use your info and changed the following but still no go. Debug error.


ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Combined!(RC[-1]:R[" & cnt & "]C9)"

 

[Richie(UK)]

Hi Jeff,

Sorry for any confusion caused (I was just trying to illustrate the idea). How about :

cnt = 11
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Combined!R1C1:R" & cnt & "C9)"