what is wrong with this r1c1 notation?

[cspengel]

Trying to change this to incorporate my last row, which is using the variable lLastRowDeDuped.

Range("B29").FormulaR1C1 = "=COUNTIFS(R[-27]C[5]:R781C[5],RC[-1],R[-27]C[10]:R[752]C[10],""<>""&""LINES SET TO 0"")"

I receive #REF error when using this. The lLastRowDeDuped works for the first use of it, but does not work in the second instance where the 752 is.

Range("B29").FormulaR1C1 = "=COUNTIFS(R[-27]C[5]:R" & lLastRowDeDuped & "C[5],RC[-1],R[-27]C[10]:R[" & lLastRowDeDuped & "]C[10],""<>""&""LINES SET TO 0"")"

Any assistance would be great!

 

[6StringJazzer]

How can you tell which instance is failing? Is the formula being entered into the cell? If so, what is the formula as it appears in the cell?

The first use is an absolute row number, and the second use is relative to the row containing the formula (29 in this case). It does not seem likely to me that you would intend to use the same number in both cases. They are not the same in the first example you gave before you added the variable. Is that actually what's intended? If so, what number is the variable when this fails?

When I try this code with a value of 752 I do not get a #REF error. (I do get a #VALUE error because I don't have any data to test with.) Here is the formula that results:

=COUNTIFS(G2:G$752,A29,L2:L781,"<>"&"LINES SET TO 0")

 

[cspengel]

How can you tell which instance is failing? Is the formula being entered into the cell? If so, what is the formula as it appears in the cell?

The first use is an absolute row number, and the second use is relative to the row containing the formula (29 in this case). It does not seem likely to me that you would intend to use the same number in both cases. They are not the same in the first example you gave before you added the variable. Is that actually what's intended? If so, what number is the variable when this fails?

When I try this code with a value of 752 I do not get a #REF error. (I do get a #VALUE error because I don't have any data to test with.) Here is the formula that results:

=COUNTIFS(G2:G$752,A29,L2:L781,"<>"&"LINES SET TO 0")

My actual formula is

=COUNTIFS(G2:G$781,A29,L2:L781,"<>"&"LINES SET TO 0")

however the last row could be 781 or it could be 1000. I just realized when using either of the above code I posted, it is returning

=COUNTIFS(G2:G$781,A29,#REF!,"<>"&"LINES SET TO 0")

You are right in stating that the number is relative to the row containing formula. What is my option? I feel like I am having brain fog.. lol

 

[cspengel]

I forgot I was deleting a few columns at end of macro therefore when I was recording a macro to make changes to the layout, it was inturn referencing the wrong columns. Doh