Ask Question Forum:
C
O
M
P
U
T
E
R
2
8
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Y-position of the mouse cursor
Attachment:===
formulaR1C1 in VBA for sum from fix cell
Modifyit means at B2= sum($A$1: A2) and at B3= sum($A$1: A3), I don't know how to set the fix cell $A$1
into formulaR1C1, and I try to use the following test ()sub code it failed, and also use
r.offset(0,1)= " =sum(addres(1,1): rc[-1]), it also fail, suppose address(1,1) will show $A$1 in the sum
but it shows as= sum(address(1,1), A2) in the excel sheet cell B2.
Please advise how to apply fix cell into formulaR1C1 like specifiy $A$1
Duncan
================================================
For example for the expect result in the excel sheet by formulaR1C1,
A B
1 1
2 2 3 '= sum($A$1:A2)
3 4 7 '=sum($A$1:A3)
4 10 17 '=sum($A$1:A4)
SUb test
dim r as range
r=range(cells(2,1), 1000,1)
r.offset(0,1)= " =sum($A$1: rc[-1])
End Sub
Think you have some extra brackets in there.
Sub FillFormula()
'
' FillFormula Macro
' Macro written 22/10/2010 by e.harrison
'
Dim strRow As String
strRow = GetInputText("Please enter row number", "Row Number")
Range("B2").Select
If CInt(strRow) > 2 Then
Dim offset As Integer
offset = CInt(strRow) - 2
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & strRow & "C1)"
Else
ActiveCell.FormulaR1C1 = "=SUM(R" & strRow & "C1:RC[-1])"
End If
Selection.AutoFill Destination:=Range("B2:B6")
End Sub
Gets a bit strange if the row value you use is higher than the first cell where the formula entered but I had a bit of go at that. I have attached the example file.
Is that something like what you were after?
Thanks
E
Attachment:Test.xlsm
Think you have some extra brackets in there.
Sub FillFormula()
'
' FillFormula Macro
' Macro written 22/10/2010 by e.harrison
'
Dim strRow As String
strRow = GetInputText("Please enter row number", "Row Number")
Range("B2").Select
If CInt(strRow) > 2 Then
Dim offset As Integer
offset = CInt(strRow) - 2
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & strRow & "C1)"
Else
ActiveCell.FormulaR1C1 = "=SUM(R" & strRow & "C1:RC[-1])"
End If
Selection.AutoFill Destination:=Range("B2:B6")
End Sub
Gets a bit strange if the row value you use is higher than the first cell where the formula entered but I had a bit of go at that. I have attached the example file.
Is that something like what you were after?
Thanks
E
Attachment:Test.xlsm
Think you have some extra brackets in there.
Sub FillFormula()
'
' FillFormula Macro
' Macro written 22/10/2010 by e.harrison
'
Dim strRow As String
strRow = GetInputText("Please enter row number", "Row Number")
Range("B2").Select
If CInt(strRow) > 2 Then
Dim offset As Integer
offset = CInt(strRow) - 2
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & strRow & "C1)"
Else
ActiveCell.FormulaR1C1 = "=SUM(R" & strRow & "C1:RC[-1])"
End If
Selection.AutoFill Destination:=Range("B2:B6")
End Sub
Gets a bit strange if the row value you use is higher than the first cell where the formula entered but I had a bit of go at that. I have attached the example file.
Is that something like what you were after?
Thanks
E
Attachment:Test.xlsm
Think you have some extra brackets in there.
Sub FillFormula()
'
' FillFormula Macro
' Macro written 22/10/2010 by e.harrison
'
Dim strRow As String
strRow = GetInputText("Please enter row number", "Row Number")
Range("B2").Select
If CInt(strRow) > 2 Then
Dim offset As Integer
offset = CInt(strRow) - 2
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & strRow & "C1)"
Else
ActiveCell.FormulaR1C1 = "=SUM(R" & strRow & "C1:RC[-1])"
End If
Selection.AutoFill Destination:=Range("B2:B6")
End Sub
Gets a bit strange if the row value you use is higher than the first cell where the formula entered but I had a bit of go at that. I have attached the example file.
Is that something like what you were after?
Thanks
E
Attachment:Test.xlsm
Think you have some extra brackets in there.
Sub FillFormula()
'
' FillFormula Macro
' Macro written 22/10/2010 by e.harrison
'
Dim strRow As String
strRow = GetInputText("Please enter row number", "Row Number")
Range("B2").Select
If CInt(strRow) > 2 Then
Dim offset As Integer
offset = CInt(strRow) - 2
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & strRow & "C1)"
Else
ActiveCell.FormulaR1C1 = "=SUM(R" & strRow & "C1:RC[-1])"
End If
Selection.AutoFill Destination:=Range("B2:B6")
End Sub
Gets a bit strange if the row value you use is higher than the first cell where the formula entered but I had a bit of go at that. I have attached the example file.
Is that something like what you were after?
Thanks
E
Attachment:Test.xlsm
Think you have some extra brackets in there.
Sub FillFormula()
'
' FillFormula Macro
' Macro written 22/10/2010 by e.harrison
'
Dim strRow As String
strRow = GetInputText("Please enter row number", "Row Number")
Range("B2").Select
If CInt(strRow) > 2 Then
Dim offset As Integer
offset = CInt(strRow) - 2
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R" & strRow & "C1)"
Else
ActiveCell.FormulaR1C1 = "=SUM(R" & strRow & "C1:RC[-1])"
End If
Selection.AutoFill Destination:=Range("B2:B6")
End Sub
Gets a bit strange if the row value you use is higher than the first cell where the formula entered but I had a bit of go at that. I have attached the example file.
Is that something like what you were after?
Thanks
E
Attachment:Test.xlsm
