Dynamic formula for changing row numbers
 

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

Dynamic formula for changing row numbers
 

Dim myCountA as long

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

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



MikeF wrote:

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

Dynamic formula for changing row numbers
 

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



Dave Peterson wrote:

Dim myCountA as long

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

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

MikeF wrote:

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

--

Dave Peterson

Dynamic formula for changing row numbers
 

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

Thanx again!!!
- Mike

"Dave Peterson" wrote:

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



Dave Peterson wrote:

Dim myCountA as long

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

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

MikeF wrote:

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

--

Dave Peterson