'/===============================================/
Sub Macro1()
'ASSUME the activecell is on the same line as the data
'ASSUME the activecell is to the right of the data
Dim iStart As Long, iEnd As Long, iActiveColumn As Long
Dim strStart As String, strEnd As String
On Error GoTo err_Sub
' - V A R I A B L E S - - - - - - - - - - - - - - -
strStart = "Ending"
strEnd = "Difference"
iActiveColumn = ActiveCell.Column
iStart = _
Application.WorksheetFunction.Match(strStart, _
Range(ActiveCell.Offset(0, -iActiveColumn + 1).Address & _
":" & ActiveCell.Offset(0, -1).Address), False)
iEnd = _
Application.WorksheetFunction.Match(strEnd, _
Range(ActiveCell.Offset(0, -iActiveColumn + 1).Address & _
":" & ActiveCell.Offset(0, -1).Address), False)
' - - - - - - - - - - - - - - - - - - - - - - - - -
ActiveCell.FormulaR1C1 = _
"=SUM(RC[" & -iActiveColumn + iStart + 1 & "]:RC[" & _
-iActiveColumn + iEnd - 1 & "])"
exit_Sub:
On Error Resume Next
ActiveCell.Offset(1, 0).Select
Exit Sub
err_Sub:
ActiveCell.ClearContents
GoTo exit_Sub
End Sub
'/===============================================/
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
Thyagaraj said:
Assuming you are starting in column A, all data in same row and the sum is
from Col A to the Col just to the left of your cursor, use this macro...
ActiveCell.FormulaR1C1 = "=SUM(RC[-" & ActiveCell.Column - 1 & "]:RC[-1])"
HTH.
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.
Thyagaraj said:
I have stuck in a problem,
By running a macro i should be able to sum a range of variable data.
Eg:-
sum of A1
1 in E1 or
sum of A1:L1 in M1
ie; exact to the next cell i want to sum all the previous cells data.
Is This Possibel........?
Thanks in Advance
This is working
ActiveCell.FormulaR1C1 = "=SUM(RC[-" & ActiveCell.Column - 1 &
"]:RC[-1])"
In the above formula can we assign a variable to the column references.
meaning
Data may be any where on the sheet but there is reference where the sum
formula should start and where it should end.
ie; before the statring cell where the data should start there is a
word "Ending" and exactly where the data should end there is a word
"Difference" - The middle data should be only summed.
Regards
Thyagaraj
