Macro to sum totals using resize

[howard]

I have a macro to sum data from Col C onwards using resize

I get a run time error "Application defined or object-defined error and the code below is highlighted

It would be appreciated if someone could kindly assist me

 Range("C" & finalrow + 3).Formula = "=sum(C2:C" & finalrow & ").rezize(,7)"

See full code

 Sub Totals()
Sheets("Data").Select
finalrow = Range("A65536").End(xlUp).Row
Range("B" & finalrow + 3).Value = "Total Value"
Range("C" & finalrow + 3).Formula = "=sum(C2:C" & finalrow & ").resize(,7)"

 

[AlphaFrog]

Resize isn't a worksheet function. Try this...

Range("C" & finalrow + 3).Formula = "=sum(C2:[COLOR=#ff0000]I[/COLOR]" & finalrow & ")"

If you must use Resize for some reason, try this...

Range("C" & finalrow + 3).Formula = "=sum(" & Range("C2:C" & finalrow).Resize(, 7).Address & ")"

 

[igold]

Hi Howard,

Not sure what you are trying to do but you cannot use .resize in an excel formula.

 

[howard]

Thanks for the help

Whst I am trying to achieve using

Range("C" & finalrow + 3).Formula = "=sum(C2:C" & finalrow & ").resize(,7)"

is to show the totals for each Col seperately using resize or offset

for eg total under C =sum(C2 to the last row in Col C for Col D =sum(D2 to last row in Col D etc instead of using

 Range("C" & finalrow + 3).Formula = "=sum(C2:C" & finalrow & ")"
Range("D" & finalrow + 3).Formula = "=sum(D2:D" & finalrow & ")" etc for each Col up to Col I

Kindly amend my code

 

[Marcelo Branco]

Try

Range("C" & finalrow + 3).Resize(, 7).Formula = "=SUM(C2:C" & finalrow & ")"

M.

 

[howard]

Thanks for the help. Your code works perfectly