Dynamic formula for changing row numbers

M

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])"
 
D

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])"
Click to expand...
 
D

Dave Peterson

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



Dave said:
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])"
Click to expand...
Click to expand...
 
M

MikeF

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

Thanx again!!!
- Mike

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



Dave said:
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])"
Click to expand...
Click to expand...
Click to expand...
 
Top