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 ?

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" <forum@OfficeKB.com> wrote in message
news:52AC0DA39AA88@OfficeKB.com...
> 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 ?

Hi Jean Pierre,

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

---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:uALy9iknFHA.4056@TK2MSFTNGP10.phx.gbl...
> 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" <forum@OfficeKB.com> wrote in message
> news:52AC0DA39AA88@OfficeKB.com...
>> 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 ?
>
>

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 ?
>

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

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
>