VBA loops

Hi All,

I am trying to create a loop to count the number of rainy days. If there is no rain the value is 0, but if it rains then the value is <0. I want to add all the days of rain in a year and the value returned should be the total number of rainy days.

Please Login or Register  to view this content.

Thanks

Why do you need VBA. You could just use COUNTIF.

=366-COUNTIF(A2:A367,0)

Regards, TMS

How about =ABS(SUMPRODUCT(SIGN(C2:C367)))

Thanks all for your reply.
I am combining this code with another code in VBA and it would have been nice to have everything in vba.
I have tried some of your suggestion but they seem to be giving something else.
i may have expressed myself incorrectly, but what i meant was to count the rainy days, not the amount of rain in each cell.

Malowle

for further help, please post a sample workbook.

the COUNTIF formula counts the days, ir doesn't sum them.

Thanks again, but the COUNTIF does give me 360.

A sample workbook please.

In your example:

C2: =COUNTA(B2:B366) = 365
D2: =COUNTIF(B2:B366,0) = 317
E2: =365-COUNTIF(B2:B366,0) = 48
F2: =D2+E2 = 365

If you drag these formulae down, they will give a 365 "rolling" total.

Code to fill the range D2:D366

Please Login or Register  to view this content.

And column E:

Please Login or Register  to view this content.

Regards, TMS

The full code:

Please Login or Register  to view this content.

Is this a new question? If it is, you should start a new thread. Either way, you'll need to provide a lot more information.


Regards, TMS

I really have no idea what that means. If you want simply to take bottom from top for each cell, then this does that:

Please Login or Register  to view this content.

Regards, TMS