FormulaR1C1, cell not referring to itself error

[gtd526]

Hello,
The following code works, but when I Offset from another cell to the correct cell and enter the formula, I receive an error (row 48 only being entered). Thank you.
Here is the formula:

Wagers.xlsm
	A
47	With Range("H" & Rows.Count).End(xlUp)
48	.FormulaR1C1 = "=IFERROR(LOOKUP(2, 1/((COUNTIF(R" & .Row - 1 & "C8:R[-1]C8,'[NFL.xlsm]Weekly Picks'!R4C8:R35C8)=0)*('[NFL.xlsm]Weekly Picks'!R4C8:R35C8<>"""")),'[NFL.xlsm]Weekly Picks'!R4C8:R35C8),"""")"
49	End With
NFL to R1C1

 

[Fluff]

Can you post the actual code in code tags, rather than a mini sheet.
Also what is the error you get?

 

[mikerickson]

The "R" & .Row - 1 part of the formula can be replaced with "R[-1]"

 

[gtd526]

Can you post the actual code in code tags, rather than a mini sheet.
Also what is the error you get?

Here is the code:

Sub Test4()
'(2)
'Offset to cell and enter formula for Reg Bets and Parlay Bets
'each formula refers to a "list", has to be 1 cell up from range, not to refer to itself, otherwise teams will flip positions
'this way it stays consistant

'enters the formula for Regular My Betting Team, list of
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row      'last row according to "B"
    Range("E" & LastRow).Select  'have to SELECT lastRow to select correct range regarding line below
    Selection.Offset(0, 3).Select
    'insert formula HERE
    'below formula .Row error when by itself, need formula without .Row
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'enters the formula for my Parlay Team, list of
'need to use IFNA to enter 0, 0 doesn't effect Parlay W/L formula, #N/A does (if error, doesn't work)

    LastRow = Cells(Rows.Count, "B").End(xlUp).Row      'last row according to "B"
    Range("E" & LastRow).Select  'have to SELECT lastRow to select correct range regarding line below
    Selection.Offset(2, 3).Select
    'insert formula HERE
'    With Range("H" & Rows.Count).End(xlUp)
        ActiveCell.FormulaR1C1 = "=IFNA(LOOKUP(2, 1/((COUNTIF(R" & .Row - 1 & "C8:R[-1]C8,'[NFL.xlsm]Weekly Picks'!R4C8:R35C8)=0)*('[NFL.xlsm]Weekly Picks'!R4C8:R35C8<>"""")*('[NFL.xlsm]Weekly Picks'!$F$4:$F$35=" * ")),'[NFL.xlsm]Weekly Picks'!R4C8:R35C8),0)"
'    End With


End Sub

Here is the error I'm receiving:

 

[gtd526]

The "R" & .Row - 1 part of the formula can be replaced with "R[-1]"

Here is the error Im receiving:

 

[RoryA]

As Mike mentioned, you're essentially doing COUNTIF(R[-1]C8:R[-1]C8 which is only one cell, so there's no need to repeat the reference.

 

[Fluff]

You are getting that error, because you have removed the With statement.
Try what mikerickson suggested.

 

[Fluff]

With the latest code, you need to remove the quotes.

 

[gtd526]

With the latest code, you need to remove the quotes.

Can you post the final formula?

 

[Fluff]

Try

ActiveCell.FormulaR1C1 = "=IFNA(LOOKUP(2, 1/((COUNTIF(R[-1]C8,'[NFL.xlsm]Weekly Picks'!R4C8:R35C8)=0)*('[NFL.xlsm]Weekly Picks'!R4C8:R35C8<>"""")*('[NFL.xlsm]Weekly Picks'!r4c6:r35c6="" * "")),'[NFL.xlsm]Weekly Picks'!R4C8:R35C8),0)"