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:D1 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

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

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Thyagaraj" wrote:

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:D1 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

Gary L Brown wrote:
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

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Thyagaraj" wrote:

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:D1 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

'/===============================================/
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

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Thyagaraj" wrote:


Gary L Brown wrote:
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

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Thyagaraj" wrote:

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:D1 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