I'm trying to create a COUNTIF formula with the R1C1 format that should look like the following after being run:

=COUNTIF(I24:I24,"<"&DP24)



I'm having trouble getting the row number variable, "less than" sign, and ampersand to work correctly when all in the same formula. What I have now (below) gives me a Run-time error 1004 (application-defined or object-defined error). Variations of what's below place a hardcoded TRUE/FALSE in the cell. Here is what I have now:

Cells(x, LastColumnNumber + 10).FormulaR1C1 = "=CountIF(R" & x & "C" & 9 & ":R" & x & "C" & LastColumnNumber & ","">"" & " & " & Cells(x, LastColumnNumber + 8).Address)"""

Any suggestions on how to make this work?

Thanks!

I think you may have a comma out of place

Here is what I tested with:

=COUNTIF(B1,"<"&TODAY()) = 1

I see with yours perhaps a comma in the wrong place

=COUNTIF(I24:I24,"<"&DP24)

Do you mean within the macro code or within the final formula I'm trying to have the code produce? The final formula works when I enter it into a cell. It's the code to produce that formula that is giving me trouble (mainly how to show the comma within a R1C1 COUNTIF after a variable column number and how to show an ampersand in the final formula when ampersands are used in creating the code).

Thanks

FYI--I got it working with the following:

Cells(x, LastColumnNumber + 10).FormulaR1C1 = "=CountIF(R" & x & "C9:R" & x & "C" & LastColumnNumber & ","">""&R" & x & "C" & LastColumnNumber + 8 & ")"