AutoSum in a Macro with conditions, Question from a newbie

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
Hi;

I'm trying to create a macro to automatically sum columns G throughR, based on a condition of a value in column C.

If C = 'Total for Customer' (text statement)

Then AutoSum Columns G through R


I am linking cells in column C from another tab in the same workbook, so I have to have the macro pick up the values I'm calling, not the formula in Column C.

How do I modify this macro below to possible use for this purpose?

Sub testautosum()
'
' testautosum Macro
'
Range("C2").Select
Cells.Find(What:="Total for Customer", After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
Range("G15").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
Range("G15").Select
Selection.AutoFill Destination:=Range("G15:R15"), Type:=xlFillDefault
Range("G15:R15").Select
End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Re: AutoSum in a Macro with conditions, Question from a newb

Hi
try
Code:
Sub testautosum()
    Dim r As Range
    Set r = Cells.Find("Total for Customer")
    If Not r Is Nothing Then
       r.Resize(,12).formulaR1C1 = "=Sum(r[-3]c:r[-1]c)"
    End If
End Sub
 

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
jindon;

this code is changing the value in column c to a date; and is there a way to make this loop through every row in the spreadsheet to change all of them at one time of execution, instead of running this multiple times?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Re: AutoSum in a Macro with conditions, Question from a newb

Ah, you wanted G:R...
try
Code:
Sub testautosum()
    Dim r As Range
    Set r = Columns("c").Find("Total for Customer")
    If Not r Is Nothing Then
       r.Offset(,4).Resize(,12).formulaR1C1 = "=Sum(r[-3]c:r[-1]c)"
    End If
End Sub
 

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164

ADVERTISEMENT

jindon;

This code is not working for me, and is there a way to make it loop automatically?

Columns G through R are needing the AutoSum, when column C has the text criteria.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
How it is not working?

Doesn't it insert the formula Co. G : R, when col.C is "Total For Customer?"
 

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164

ADVERTISEMENT

in my spreadsheet, it's stopping on row 47, it does not advance through the rest of the spreadsheet.

The other code worked, but I had to execute it for one row at a time.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
So you mean you have multiple "Total For Customer" in Col.C?

If so, what are the formula for them?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
In your original code,

When "Total For Customer" found in Col.C,

Insert formula that is Sum(r[-3]c:r[-1]c) into Col.G To R

What exactly do you want to do?
 

wazzulu1

Board Regular
Joined
Oct 4, 2006
Messages
164
I forgot to mention that there are multiple customers that are sorted, and there is a row that the autosum formula needs to go into for each in columns g through r, when column c has the criteria.

So there are multiple rows that need this action.
 

Forum statistics

Threads
1,141,757
Messages
5,708,340
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top