XL/VBA:Why do I get this error(Range.FormulaR1C1)?

PaLPiTaTioN · December 24th, 2004, 08:27 AM

I use this code to put a formula in a range of cells. If I run the code, I get the VBA-error "1004 Application-defined or object-defined error". If I then place the same formula in a cell on a worksheet in Excel, the calculations are done as expected. Why can't I place the formula from VBA?

Code:

shtMenu.Range(Cells(iMenuMinRow, iTblBaseColumn + 5), Cells(iMenuMaxRow, iTblBaseColumn + 5)).FormulaR1C1 = _
      "=SUMPRODUCT((Brongegevens!R2C1:R65536C1=RC[-4])*(Brongegevens!R2C5:R65536C5=RC[-2]);Brongegevens!R2C14:R65536C14)"

shattered · December 29th, 2004, 10:06 AM

excel is looking for a standard cell reference rather than the R1C1 reference style. Excel will understand the notation but vba won't.

PaLPiTaTioN · January 3rd, 2005, 04:51 AM

You first say that Excel will require a standard reference (A1). In the second sentence you say that it is VBA that will not understand the R1C1-notation and Excel will. Isn't that contradictory?
Thank you for your reply anyway!

amjad_mahmood · January 3rd, 2005, 08:27 AM

I have changed your code as follows and it seems it's working!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Range(Cells(Target.Row, Target.Column + 5), Cells(Target.Row, Target.Column + 5)).FormulaR1C1 = _
"=SUMPRODUCT((Brongegevens!R2C1:R65536C1=RC[-4])*(Brongegevens!R2C5:R65536C5=RC[-2]),Brongegevens!R2C14:R65536C14)"

End Sub

NOTE: the semi-collon(;) is replaced with coma (,)