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.

Option explicit 

Dim n1 As Double
Dim n2 As Double
Dim minimum As Double

For k = 0 To 365


' Get numbers from the spreadsheet
 n1 = Worksheets("Rainfall").Cells(k + 2, 3)

 
' find the number of rainy days
If n1 > 0 Then
  n1 = rainyday
Else
  n1 = n2
End If

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

With Range("D2:D366")
    .FormulaR1C1 = _
        "=COUNTIF(RC[-2]:R[364]C[-2],0)"
    .Value = .Value
End With

And column E:

With Range("E2:E366")
    .FormulaR1C1 = _
        "=365-COUNTIF(RC[-3]:R[364]C[-3],0)"
    .Value = .Value
End With

Regards, TMS

The full code:

' standard module: mRainfall
Sub Rainfall()

' determine last row of data
Dim lLR As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
' =COUNTIF(B2:B366,0)
With Range("D2:D" & lLR)
    .FormulaR1C1 = _
        "=COUNTIF(RC[-2]:R[364]C[-2],0)"
    .Value = .Value
End With
  
' =365-COUNTIF(B2:B366,0)
With Range("E2:E" & lLR)
    .FormulaR1C1 = _
        "=365-COUNTIF(RC[-3]:R[364]C[-3],0)"
    .Value = .Value
End With

' =D2+E2
With Range("F2:F" & lLR)
    .FormulaR1C1 = _
        "=RC[-2]+RC[-1]"
    .Value = .Value
End With
Application.ScreenUpdating = True

End Sub

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:

Sub AquiferCalc()

Dim lLR As Long
Application.ScreenUpdating = False
With Sheets("Top")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Sheets("New Bottom")
    .Range("A1:IV" & lLR).FormulaR1C1 = _
        "=Top!RC-Bottom!RC"
End With
Application.ScreenUpdating = True

End Sub

Regards, TMS