Error 1004 with FormulaR1C1 **** Newbee, Help Please!!

[amisagi]

Hi, I am new to VB coding and deperately need help...
I have racked my brain but cant find the cause of error 1004 with this particualr line (highlighted in Red).

Dim X as Long
Dim iRow, iCol, intMonths As Integer

iRow = X * intMonths 'X is the number of rows in Sheet1 with data
iCol = intMonths - 2 ' I have a seperate module to calculate intMonths and it is working perfect.
Range("addrPerfCol")(RowToFill, 1) ' Works perfect, selects the correct row, column

ActiveCell.FormulaR1C1 = "=IFERROR(IF(Sheet1!R[-iRow]C[iCol]="""",""DNR"",IF(Sheet1!R[-iRow]C[iCol]>=Sheet1!R[-iRow]C8,""M"",""NM"")),"""")"

Many thanks in advance, if there is any other info required please let me know...

 

[StephenCrump]

iRow and iCol are VBA variables that mean nothing to Excel, so your formula string needs to pass their values to Excel, like this:

Range("addrPerfCol")(RowToFill, 1).FormulaR1C1 = _
    "=IFERROR(IF(Sheet1!R[-" & iRow & "]C[" & iCol & "]="""",""DNR"",IF(Sheet1!R[-" _
    & iRow & "]C[" & iCol & "]>=Sheet1!R[-" & iRow & "]C8,""M"",""NM"")),"""")"

PS: Where's my manners? Welcome to the Forum!

 

[amisagi]

ah ha!!
Thank you Steven!!

 

[StephenCrump]

You're welcome. Have fun coding!