Macro to Sum Two Adjacent Columns as per Text in previous Column

  • Thread starter Thread starter Reha
  • Start date Start date
R

Reha

Hello All,
I found this macro from the Groups which sums Columns according to the
Value of 'x'.

Sub test() ' Macro to Sum Columns
Dim lrow As Long, x As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
' sum the columns - change x according to your needs
For x = 1 To 2
lrow = ws.Cells(Rows.Count, x).End(xlUp).Offset(1).Row
If Application.Sum(Range(ws.Cells(2, x),
ws.Cells(lrow, x))) > 0 Then
With ws.Cells(lrow, x)
.Formula = "=Sum(" & Range(ws.Cells(2,
x), ws.Cells(lrow - 1, x)).Address(0, 0) & ")"
.NumberFormat = "#,###"
End With
End If
Next
End Sub

My requirement is to search for a text appearing in Sheet1 and Sum
adjacent Columns.
No. of Rows would be varying.

For eg: lookup the text 'Grade' and Sum adjacent two columns.
If the text 'Grade' appears in
Column B then Sum Col C and D...
if in F then Sum Columns G and H...
if in T then Sum Columns U and V

and so on...

Any help would be greatly appreciate.
Thanks and regards
Reha
 
Hello All,
I found this macro from the Groups which sums Columns according to the
Value of 'x'.

Sub test() ' Macro to Sum Columns
Dim lrow As Long, x As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
    ' sum the columns - change x according to your needs
            For x = 1 To 2
            lrow = ws.Cells(Rows.Count, x).End(xlUp).Offset(1).Row
                  If Application.Sum(Range(ws.Cells(2, x),
ws.Cells(lrow, x))) > 0 Then
                        With ws.Cells(lrow, x)
                              .Formula = "=Sum(" & Range(ws.Cells(2,
x), ws.Cells(lrow - 1, x)).Address(0, 0) & ")"
                              .NumberFormat= "#,###"
                        End With
                  End If
            Next
End Sub

My requirement is to search for a text appearing in Sheet1 and Sum
adjacent Columns.
No. of Rows would be varying.

For eg: lookup the text 'Grade' and Sum adjacent two columns.
If the text 'Grade' appears in
Column B then Sum Col C and D...
if in F then Sum Columns G and H...
if in T then Sum Columns U and V

and so on...

Any help would be greatly appreciate.
Thanks and regards
Reha
Click to expand...

Try this to find the last row, find the text in row 1 and sum columns
Sub SumColumnsSAS()
'SalesAid Software (e-mail address removed)
Dim lr As Long
Dim i
Dim mc As Long
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
For i = 1 To 2
mc = Rows(1).Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Column - 1
'MsgBox mc
Cells(lr, mc + i) = _
Application.Sum(Range(Cells(1, mc + i), Cells(lr, mc + i)))
Next i
End Sub
 
Try this to find the last row, find the text in row 1 and sum columns
Sub SumColumnsSAS()
'SalesAid Software (e-mail address removed)
Dim lr As Long
Dim i
Dim mc As Long
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
For i = 1 To 2
mc = Rows(1).Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Column - 1
'MsgBox mc
Cells(lr, mc + i) = _
Application.Sum(Range(Cells(1, mc + i), Cells(lr, mc + i)))
Next i
End Sub- Hide quoted text -

- Show quoted text -
Click to expand...

Hello,
My requirement is to look up for 'Grade' in any Row and put the sum
formula in the next two columns.
Let me put up an example

Primary Class 1
1 2
1 2
1 3
1 2
1 2
Grade 5 11 <<< The macro puts up value here...I need sum
formula in both the columns.

As you can see above ...Grade can appear in any Row..

Hope I am clear now
Thanks
 
Hello,
My requirement is to look up for 'Grade' in any Row and put the sum
formula in the next two columns.
Let me put up an example

        Primary Class 1
        1       2
        1       2
        1       3
        1       2
        1       2
Grade   5       11        <<< The macro puts up value here...I need sum
formula in both the columns.

As you can see above ...Grade can appear in any Row..

Hope I am clear now
Thanks- Hide quoted text -

- Show quoted text -
Click to expand...

Sub SumColumns1SAS()
'SalesAid Software (e-mail address removed)
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub
 
On Aug 11, 4:46 pm, Don Guillett Excel MVP <[email protected]>
wrote:
Click to expand...
Hello,
My requirement is to look up for 'Grade' in any Row and put the sum
formula in the next two columns.
Let me put up an example
Click to expand...
        Primary Class 1
        1       2
        1       2
        1       3
        1       2
        1       2
Grade   5       11        <<< The macro puts up value here...I need sum
formula in both the columns.
Click to expand...
As you can see above ...Grade can appear in any Row..
Click to expand...
Hope I am clear now
Thanks- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Sub SumColumns1SAS()
'SalesAid Software (e-mail address removed)
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks a lot for your help
 
On Aug 11, 4:46 pm, Don Guillett Excel MVP <[email protected]>
wrote:
Click to expand...
Hello,
My requirement is to look up for 'Grade' in any Row and put the sum
formula in the next two columns.
Let me put up an example
Click to expand...
        Primary Class 1
        1       2
        1       2
        1       3
        1       2
        1       2
Grade   5       11        <<< The macro puts up value here...I need sum
formula in both the columns.
Click to expand...
As you can see above ...Grade can appear in any Row..
Click to expand...
Hope I am clear now
Thanks- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Sub SumColumns1SAS()
'SalesAid Software (e-mail address removed)
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub- Hide quoted text -

- Show quoted text -
Click to expand...

To add to my previous reply.
'A small change would be more beneficial to my requirement.
If in the macro I can specify the starting Row that would really
helpful.
Now the macro takes up everything from Row 1 till the end
If the data heading starts from Row 4 then the macro should calculate
from Row 5 till the end.
I can change the row number as per my requirement whenever required
Hope I am clear
Thanks once again
 
Sub SumColumns1SAS()
'SalesAid Software (e-mail address removed)
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

To add to my previous reply.
'A small change would be more beneficial to my requirement.
If in the macro I can specify the starting Row that would really
helpful.
Now the macro takes up everything from Row 1 till the end
If the data heading starts from Row 4 then the macro should calculate
from Row 5 till the end.
I can change the row number as per my requirement whenever required
Hope I am clear
Thanks once again- Hide quoted text -

- Show quoted text -
Click to expand...

You should really learn to state your needs in your FIRST posting to
preclude needless work from those of us offering our free assistance.
For custom programming contact me privately at dguillett @gmail.com
with your file.
 
On Aug 11, 11:04 pm, Don Guillett Excel MVP <[email protected]>
wrote:
On Aug 11, 4:46 pm, Don Guillett Excel MVP <[email protected]>
wrote:
Hello All,
I found this macro from the Groups which sums Columns accordingto the
Value of 'x'.
Sub test() ' Macro to Sum Columns
Dim lrow As Long, x As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
    ' sum the columns - change x according to your needs
            For x = 1 To 2
            lrow = ws.Cells(Rows.Count, x).End(xlUp).Offset(1).Row
                  If Application.Sum(Range(ws..Cells(2, x),
ws.Cells(lrow, x))) > 0 Then
                        With ws.Cells(lrow, x)
                              .Formula = "=Sum(" & Range(ws.Cells(2,
x), ws.Cells(lrow - 1, x)).Address(0, 0) & ")"
                              .NumberFormat = "#,###"
                        End With
                  End If
            Next
End Sub
My requirement is to search for a text appearing in Sheet1 and Sum
adjacent Columns.
No. of Rows would be varying.
For eg: lookup the text 'Grade' and Sum adjacent two columns.
If the text 'Grade' appears in
Column B then Sum Col C and D...
if in F then Sum Columns G and H...
if in T then Sum Columns U and V
and so on...
Any help would be greatly appreciate.
Thanks and regards
Reha
Try this to find the last row, find the text in row 1 and sum columns
Sub SumColumnsSAS()
'SalesAid Software (e-mail address removed)
Dim lr As Long
Dim i
Dim mc As Long
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
For i = 1 To 2
mc = Rows(1).Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Column - 1
'MsgBox mc
Cells(lr, mc + i) = _
Application.Sum(Range(Cells(1, mc + i), Cells(lr, mc + i)))
Next i
End Sub- Hide quoted text -
- Show quoted text -
Hello,
My requirement is to look up for 'Grade' in any Row and put the sum
formula in the next two columns.
Let me put up an example
        Primary Class 1
        1       2
        1       2
        1       3
        1       2
        1       2
Grade   5       11        <<< The macro puts up value here...I need sum
formula in both the columns.
As you can see above ...Grade can appear in any Row..
Hope I am clear now
Thanks- Hide quoted text -
- Show quoted text -
Sub SumColumns1SAS()
'SalesAid Software (e-mail address removed)
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
To add to my previous reply.
'A small change would be more beneficial to my requirement.
If in the macro I can specify the starting Row that would really
helpful.
Now the macro takes up everything from Row 1 till the end
If the data heading starts from Row 4 then the macro should calculate
from Row 5 till the end.
I can change the row number as per my requirement whenever required
Hope I am clear
Thanks once again- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

You should really learn to state your needs in your FIRST posting to
preclude needless work from those of us offering our free assistance.
For custom programming contact me privately at dguillett @gmail.com
with your file.- Hide quoted text -

- Show quoted text -
Click to expand...

I will keep in mind your suggestion for future postings. Thanks for
your time.
 
On Aug 11, 11:04 pm, Don Guillett Excel MVP <[email protected]>
wrote:
On Aug 11, 4:46 pm, Don Guillett Excel MVP <[email protected]>
wrote:
Hello All,
I found this macro from the Groups which sums Columns according to the
Value of 'x'.
Sub test() ' Macro to Sum Columns
Dim lrow As Long, x As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
    ' sum the columns - change x according to your needs
            For x = 1 To 2
            lrow = ws.Cells(Rows.Count, x).End(xlUp).Offset(1).Row
                  If Application.Sum(Range(ws.Cells(2, x),
ws.Cells(lrow, x))) > 0 Then
                        With ws.Cells(lrow, x)
                              .Formula = "=Sum(" & Range(ws.Cells(2,
x), ws.Cells(lrow - 1, x)).Address(0, 0) & ")"
                              .NumberFormat = "#,###"
                        End With
                  End If
            Next
End Sub
My requirement is to search for a text appearing in Sheet1 and Sum
adjacent Columns.
No. of Rows would be varying.
For eg: lookup the text 'Grade' and Sum adjacent two columns.
If the text 'Grade' appears in
Column B then Sum Col C and D...
if in F then Sum Columns G and H...
if in T then Sum Columns U and V
and so on...
Any help would be greatly appreciate.
Thanks and regards
Reha
Try this to find the last row, find the text in row 1 and sum columns
Sub SumColumnsSAS()
'SalesAid Software (e-mail address removed)
Dim lr As Long
Dim i
Dim mc As Long
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
For i = 1 To 2
mc = Rows(1).Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Column - 1
'MsgBox mc
Cells(lr, mc + i) = _
Application.Sum(Range(Cells(1, mc + i), Cells(lr, mc + i)))
Next i
End Sub- Hide quoted text -
- Show quoted text -
Hello,
My requirement is to look up for 'Grade' in any Row and put the sum
formula in the next two columns.
Let me put up an example
        Primary Class 1
        1       2
        1       2
        1       3
        1       2
        1       2
Grade   5       11        <<< The macro puts up value here...I need sum
formula in both the columns.
As you can see above ...Grade can appear in any Row..
Hope I am clear now
Thanks- Hide quoted text -
- Show quoted text -
Sub SumColumns1SAS()
'SalesAid Software (e-mail address removed)
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub- Hide quoted text -
- Show quoted text -
To add to my previous reply.
'A small change would be more beneficial to my requirement.
If in the macro I can specify the starting Row that would really
helpful.
Now the macro takes up everything from Row 1 till the end
If the data heading starts from Row 4 then the macro should calculate
from Row 5 till the end.
I can change the row number as per my requirement whenever required
Hope I am clear
Thanks once again- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
You should really learn to state your needs in your FIRST posting to
preclude needless work from those of us offering our free assistance.
For custom programming contact me privately at dguillett @gmail.com
with your file.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I will keep in mind your suggestion for future postings.  Thanks for
your time.- Hide quoted text -

- Show quoted text -
Click to expand...
Change this to suit

"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
 
Back
Top