AutoSum in a Macro with conditions, Question from a newbie

[wazzulu1]

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

 

[jindon]

Re: AutoSum in a Macro with conditions, Question from a newb

Hi
try

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]

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]

Re: AutoSum in a Macro with conditions, Question from a newb

Ah, you wanted G:R...
try

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]

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]

How it is not working?

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

 

[wazzulu1]

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]

So you mean you have multiple "Total For Customer" in Col.C?

If so, what are the formula for them?

 

[jindon]

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]

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.