Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting formulas with R1C1 in VBA and eliminate $
I'm setting formulas in cells in VBA using R1C1
For example: In Excel: A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1 In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1 Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" & ColNum & ")" The resulting Excel formula in A1: =Sum($A$2:$A$10) This works fine however I would like to omit the anchors should end users need to change rows/columns around at a later date. So that ending result would be A1=Sum(A2:A10) Any suggestions? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting formulas with R1C1 in VBA and eliminate $
You need to add square brackets like below Cells(1, 1) = "=Sum(R[" & RowStart & "]C[" & ColNum & "]:R[" & RowEnd & "]C[" & ColNum & "])" "Danelo" wrote: I'm setting formulas in cells in VBA using R1C1 For example: In Excel: A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1 In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1 Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" & ColNum & ")" The resulting Excel formula in A1: =Sum($A$2:$A$10) This works fine however I would like to omit the anchors should end users need to change rows/columns around at a later date. So that ending result would be A1=Sum(A2:A10) Any suggestions? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting formulas with R1C1 in VBA and eliminate $
Cells(1, 1) = "=Sum(R[" & RowStart - 1 & "]C[" & ColNum - 1 & "]:R[" & RowEnd - 1 & "]C[" & ColNum - 1 & "])" -- __________________________________ HTH Bob "Danelo" wrote in message ... I'm setting formulas in cells in VBA using R1C1 For example: In Excel: A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1 In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1 Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" & ColNum & ")" The resulting Excel formula in A1: =Sum($A$2:$A$10) This works fine however I would like to omit the anchors should end users need to change rows/columns around at a later date. So that ending result would be A1=Sum(A2:A10) Any suggestions? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting formulas with R1C1 in VBA and eliminate $
Dim RowStart as long dim RowEnd as long dim ColNum as long dim myRng as range rowstart = 2 rowend = 10 colnum = 1 with worksheets("somesheetnamehere") 'or activesheet??? set myRng = .range(.cells(rowstart, colnum),.cells(rowend,colnum)) .cells(1,1).formula = "=sum(" & myrng.address(0,0) & ")" end with 'another way to set that range: set myrng = .cells(rowstart,colnum).resize(rowend - rowstart - 1,1) Danelo wrote: I'm setting formulas in cells in VBA using R1C1 For example: In Excel: A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1 In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1 Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" & ColNum & ")" The resulting Excel formula in A1: =Sum($A$2:$A$10) This works fine however I would like to omit the anchors should end users need to change rows/columns around at a later date. So that ending result would be A1=Sum(A2:A10) Any suggestions? Thanks! -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting formulas with R1C1 in VBA and eliminate $
Joel - thanks for your reply
Still a problem though... I think your solution would require the starting range of the cells to be activated (in order to set the correct reference point). For example if the sum (or any other formula for that matter) is pointing to a cell or range of cells in another column or even another sheet it gets a bit more cumbersome to have to activate the range prior to setting the formula. I am literally setting formulas to thousands of cells - seems like a lot of overhead. Any thoughts? "Joel" wrote: You need to add square brackets like below Cells(1, 1) = "=Sum(R[" & RowStart & "]C[" & ColNum & "]:R[" & RowEnd & "]C[" & ColNum & "])" "Danelo" wrote: I'm setting formulas in cells in VBA using R1C1 For example: In Excel: A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1 In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1 Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" & ColNum & ")" The resulting Excel formula in A1: =Sum($A$2:$A$10) This works fine however I would like to omit the anchors should end users need to change rows/columns around at a later date. So that ending result would be A1=Sum(A2:A10) Any suggestions? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting formulas with R1C1 in VBA and eliminate $
And yet another way
Sub tryme() RowStart = 2: RowEnd = 10: ColNum = 1 With Cells(1, 1) Set Rng = Range(.Offset(1), .Offset(RowEnd - 1)) .Formula = _ "=Sum(" & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")" End With End Sub adapted from example on page 111 of "Excel 2007 VBA by Green, Bullen, Bovey & Alexander best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Danelo" wrote in message ... I'm setting formulas in cells in VBA using R1C1 For example: In Excel: A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1 In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1 Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" & ColNum & ")" The resulting Excel formula in A1: =Sum($A$2:$A$10) This works fine however I would like to omit the anchors should end users need to change rows/columns around at a later date. So that ending result would be A1=Sum(A2:A10) Any suggestions? Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting formulas with R1C1 in VBA and eliminate $
I want to thank all of you who responded. Your suggestions were all greatly
appreciated. I thought you should know I ended up a creating routine that loops through all the cells with formulas and removed the "$" from the formula. It appears to be working well and alleviated the nuisance of having VBA code set all the ranges prior to concatenating the original formulas. "Danelo" wrote: I'm setting formulas in cells in VBA using R1C1 For example: In Excel: A1 = Sum(A2:A10) 'Assume RowStart = 2 RowEnd =10 ColNum =1 In VBA: 'Assume RowStart = 2 RowEnd =10 ColNum =1 Cells(1,1) = "=Sum(R" & RowStart & "C" & ColNum & ":R" & RowEnd & "C" & ColNum & ")" The resulting Excel formula in A1: =Sum($A$2:$A$10) This works fine however I would like to omit the anchors should end users need to change rows/columns around at a later date. So that ending result would be A1=Sum(A2:A10) Any suggestions? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminate #N/A in Vlookup formulas | Excel Worksheet Functions | |||
R1C1 - how can I permanently undo this setting in Excel? | Setting up and Configuration of Excel | |||
Using Variables in R1C1 formulas | Excel Programming | |||
Setting Range Name using R1C1 | Excel Programming | |||
Is there a way to eliminate specific cells from formulas? | New Users to Excel |