R1C1 dynamic range syntax error

[doodlecat]

Can anyone please tell me why the R1C1 formula line is giving me syntax error? I am using excel 2003. I have a dynamic range of data on report tab and trying to copy from ABC pivot table (on ABC pivot tab) to the report tab starting at the end of the dynamic range of data. I am trying to auto fill the blank cells from pivot table on the report tab using the "if" formula. Hope this is clear..
Thanks a lot!!!

Dim RowCount As Long, myCountRange As Range

Set myCountRange = Columns("C:C")
RowCount = Application.WorksheetFunction.CountA(myCountRange)
ActiveCell.FormulaR1C1 = "=IF('ABC Pivot'!R["&-RowCount&"]C="",Report!R[-1]C,'ABC Pivot'!R["&-RowCount&"]C)"

 

[Joe4]

Welcome to the Board!

Why do you have the negative sign in front of "RowCount" outside of the quoted area? If you want it as part of the formula, move it inside of the quotes.

 

[Norie]

You need to double up th quotes in the formula.


ActiveCell.FormulaR1C1 = "=IF('ABC Pivot'!R[" & -RowCount & "]C="""",Report!R[-1]C,'ABC Pivot'!R[" & -RowCount & "]C)"

 

[doodlecat]

Thank you for your reply, Joe!

My active cell is Cell CXXX, so my thought was to do a row count and reference the cell back to ABC Pivot! row1.

e.g. if my active cell (first blank cell in column C) is C4 on report tab, the formula will reference to
ActiveCell.FormulaR1C1 = "=IF('ABC Pivot'!R[-3]C="",Report!R[-1]C,'ABC Pivot'!R[-3]C)"

 

[Norie]

If you want to reference row 1 on a sheet using R1C1 use R1C.

ActiveCell.FormulaR1C1 = "=IF('ABC Pivot'!R1C="""",Report!R[-1]C,'ABC Pivot'!R1C)"

 

[doodlecat]

Thank you, Norie! It still gives me "Compile Error: Expected: End of Statement", after doubling up the quotes.

 

[Norie]

What exactly did you try?

Both sets of code I posted work without error.

 

[doodlecat]

Hi Norie,

I am trying to copy data from pivot table on ABC pivot table tab to report tab and fill out the blank cells generated from the pivot table (e.g. ten transactions in a day. Date will only appear once on pivot table in column A and 10 transactions are listed on column B). My main problem is that I already have an dynamic range of existing data on report tab (ABC pivot table data will be pasted down at the end of the existing data). I don't know which is my end row every time I run the macro, so I added a count row function to help me to locate the last row of current existing data on report tab. Thank you for your patience!

ActiveCell.FormulaR1C1 = "=IF('ABC Pivot'!R1C="""",Report!R[-1]C,'ABC Pivot'!R1C)"

This doesn't give me an error but I can't use absolute reference, as I will use the same formula to copy the pivot table to the report tab. Thanks!

 

[mikerickson]

You might have too few quotes
ActiveCell.FormulaR1C1 = "=IF('ABC Pivot'!R1C="""""",Report!R[-1]C,'ABC Pivot'!R1C)"

one " to start the literal, two "" for the first " in the result, two more for the second " in the result and a final " to close the literal = six.

 

[doodlecat]

I did a little twist to Norie's suggestion. It works now!! Thank you so much for everyone's answer!!!! I truely appreciate everyone's response!!!