how to sum a complete colum on another sheet in VBA
 

Hi,
I'am a novice on VBA programming....
On worksheet1 i have cell A1 which must be filled with the sum of colum P on
worksheet2 (without going back and forth between the two sheets)

in excel i would use the formula sum(P:P) on sheet 2 in eg cel A1 en step 2
would be to link cel A1 in sheet1 tot A1 in sheet2....

Does anyone have a clue how to do that in VBA ?

how to sum a complete colum on another sheet in VBA
 

Hi Jean-Pierre,

Try:

Sub TestIt()
Dim MySum As Double

MySum = Application.Sum(Sheets("Sheet2").Columns("P:P"))
MsgBox MySum

End Sub



---
Regards,
Norman



"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi,
I'am a novice on VBA programming....
On worksheet1 i have cell A1 which must be filled with the sum of colum P
on
worksheet2 (without going back and forth between the two sheets)

in excel i would use the formula sum(P:P) on sheet 2 in eg cel A1 en step
2
would be to link cel A1 in sheet1 tot A1 in sheet2....

Does anyone have a clue how to do that in VBA ?

how to sum a complete colum on another sheet in VBA
 

Worksheets("Sheet1").Range("a1") = "=SUM(Sheet2!P:P)"

"Jean-Pierre D via OfficeKB.com" wrote:

Hi,
I'am a novice on VBA programming....
On worksheet1 i have cell A1 which must be filled with the sum of colum P on
worksheet2 (without going back and forth between the two sheets)

in excel i would use the formula sum(P:P) on sheet 2 in eg cel A1 en step 2
would be to link cel A1 in sheet1 tot A1 in sheet2....

Does anyone have a clue how to do that in VBA ?

how to sum a complete colum on another sheet in VBA
 

Hi Jean Pierre,

My suggestion returns a static value, so go with Topper's dynamic formula
approach.

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Jean-Pierre,

Try:

Sub TestIt()
Dim MySum As Double

MySum = Application.Sum(Sheets("Sheet2").Columns("P:P"))
MsgBox MySum

End Sub



---
Regards,
Norman



"Jean-Pierre D via OfficeKB.com" wrote in message
...
Hi,
I'am a novice on VBA programming....
On worksheet1 i have cell A1 which must be filled with the sum of colum P
on
worksheet2 (without going back and forth between the two sheets)

in excel i would use the formula sum(P:P) on sheet 2 in eg cel A1 en step
2
would be to link cel A1 in sheet1 tot A1 in sheet2....

Does anyone have a clue how to do that in VBA ?

how to sum a complete colum on another sheet in VBA
 

Hi Toppers,

Thanks for the formula but the result is tha in cell "A1" the formula is put
and that is not what i wanted. I would like to have the result of the formula
eg the sum of the column in cell "A1"
Can you help me please?
Thanks,
Jean-Pierre (novice)

Toppers wrote:
Worksheets("Sheet1").Range("a1") = "=SUM(Sheet2!P:P)"

Hi,
I'am a novice on VBA programming....
[quoted text clipped - 5 lines]

Does anyone have a clue how to do that in VBA ?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1

how to sum a complete colum on another sheet in VBA
 

The result in A! is the value as the formula is automatically evaluated.

Otherwise, use Norman's code and add:

Worksheets("Sheet1").Range("a1")=mysum

HTH

"Jean-Pierre D via OfficeKB.com" wrote:

Hi Toppers,

Thanks for the formula but the result is tha in cell "A1" the formula is put
and that is not what i wanted. I would like to have the result of the formula
eg the sum of the column in cell "A1"
Can you help me please?
Thanks,
Jean-Pierre (novice)

Toppers wrote:
Worksheets("Sheet1").Range("a1") = "=SUM(Sheet2!P:P)"

Hi,
I'am a novice on VBA programming....
[quoted text clipped - 5 lines]

Does anyone have a clue how to do that in VBA ?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1