Dynamic formula for changing row numbers

[MikeF]

In the formula below, R8 sometimes needs to be R6, sometimes R10, sometimes
R3, and so on.

It can be derived from the COUNTA of cells v1:v20 .

Tried doing a variable called myChngRow and tying it into the formula below,
but kept getting errors.

If anyone has the solution it would be sincerely appreciated.
Thank you in advance.
Regards,
- Mike


Selection.FormulaR1C1 = "=-SUMIFS(R8C26:R201C26,R8C19:R201C19,RC[-7])"

 

[Dave Peterson]

Dim myCountA as long

mycounta = application.counta(activesheet.range("v1:v20")

selection.formular1c1 _
= "=-SUMIFS(R" & mycounta & "C26:R201C26,R" & mycounta & "C19:R201C19,RC[-7])"

In the formula below, R8 sometimes needs to be R6, sometimes R10, sometimes
R3, and so on.

It can be derived from the COUNTA of cells v1:v20 .

Tried doing a variable called myChngRow and tying it into the formula below,
but kept getting errors.

If anyone has the solution it would be sincerely appreciated.
Thank you in advance.
Regards,
- Mike

Selection.FormulaR1C1 = "=-SUMIFS(R8C26:R201C26,R8C19:R201C19,RC[-7])"

 

[Dave Peterson]

I dropped a closing paren on this line:
mycounta = application.counta(activesheet.range("v1:v20")) '<-- added final )

Dim myCountA as long

mycounta = application.counta(activesheet.range("v1:v20")

selection.formular1c1 _
= "=-SUMIFS(R" & mycounta & "C26:R201C26,R" & mycounta & "C19:R201C19,RC[-7])"

In the formula below, R8 sometimes needs to be R6, sometimes R10, sometimes
R3, and so on.

It can be derived from the COUNTA of cells v1:v20 .

Tried doing a variable called myChngRow and tying it into the formula below,
but kept getting errors.

If anyone has the solution it would be sincerely appreciated.
Thank you in advance.
Regards,
- Mike

Selection.FormulaR1C1 = "=-SUMIFS(R8C26:R201C26,R8C19:R201C19,RC[-7])"

 

[MikeF]

Dave, that's EXACTLY what I was looking for.
Your tweak made it happen.

Thanx again!!!
- Mike

I dropped a closing paren on this line:
mycounta = application.counta(activesheet.range("v1:v20")) '<-- added final )

Dim myCountA as long

mycounta = application.counta(activesheet.range("v1:v20")

selection.formular1c1 _
= "=-SUMIFS(R" & mycounta & "C26:R201C26,R" & mycounta & "C19:R201C19,RC[-7])"

In the formula below, R8 sometimes needs to be R6, sometimes R10, sometimes
R3, and so on.

It can be derived from the COUNTA of cells v1:v20 .

Tried doing a variable called myChngRow and tying it into the formula below,
but kept getting errors.

If anyone has the solution it would be sincerely appreciated.
Thank you in advance.
Regards,
- Mike

Selection.FormulaR1C1 = "=-SUMIFS(R8C26:R201C26,R8C19:R201C19,RC[-7])"