| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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])" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
| Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Excel Programming | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Way To Copy Formula In Column Without Changing All Cell Numbers | Excel Discussion (Misc queries) | |||
| Putting numbers in excel without it changing into a formula? | Excel Worksheet Functions | |||
![MikeF[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
