PDA

View Full Version : [SOLVED] vba macro code for counting total days above 32 degrees, 33, 34, and goes on..



Dharani
08-25-2013, 10:40 PM
VBA MACRO CODE FOR COUNTING TOTAL DAYS ABOVE 32 DEG TEMP, 33, 34, AND GOES ON..

Respected Sir /madam,
I need VBA MACRO CODE for the following:
from B,C columns i need totally how many days temp is 32 degrees and above, 33 degrees and above, 34 degrees and above, goes on.. i have did manually for your easy understanding. that is , from C column, in the given excel sheet it is 1922 days,out of 1922 days, 306 days has got 32 deg temp, 349 days has got 33 deg temp, goes on..

important note: in C column we have decimal values also like 32. 5, 32.4, 32.7, 33.5, 33.9, 33.4, etc., which all must come under 32 and 33 category in the required output respectively. VALUES LESS THAN 32 AND ALL NO NEED.

hope i m clear and not confusing... please find the attached excel sheet for your kind reference...

please do help me... I need very urgently...please...

Thanks & Regards,
Dharani Suresh

raj85
08-26-2013, 01:26 AM
It was really nice excersice for me.
Please check code below:
Goto VBE widow and add New module and use this code.


Option Explicit
Sub count_temp_days()
Dim N As Integer, Temp As Byte
Sheet1.Range("F6", Sheet1.Range("F6").End(xlToRight)).ClearContents
N = 2
While Sheet1.Range("A" & N).Value <> ""

If Sheet1.Range("C" & N).Value >= 32 Then
Temp = Evaluate("=rounddown(" & Sheet1.Range("C" & N).Value & ",0)")
Sheet1.Range("E6").Offset(0, Temp - 31).Value = Sheet1.Range("E6").Offset(0, Temp - 31).Value + 1
End If
N = N + 1
Wend
End Sub


You can do changes if you wish to.:hi:

Kenneth Hobs
08-26-2013, 05:40 AM
Why not use a formula?
F6=COUNTIF($C$2:$C$1923,">="&F5)

raj85
08-27-2013, 08:22 AM
Dharani,

Please mark this thred as resolved if you are satisfy with resolution provided.

salmogomes
08-27-2013, 09:56 AM
You can do it this way too: In your workbook, access the VBE (alt+F11), create a new module and paste this:Sub countdays()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Integer
Dim j As Integer
Dim somofdays As Integer


For j = 6 To 25

If Cells(5, j) = "" Then Exit For

somofdays = 0

For i = 2 To 2000

If Cells(i, 3) = "" Then Exit For

If Cells(i, 3) >= Cells(5, j) And Cells(i, 3) < Cells(5, j + 1) Then

somofdays = somofdays + 1

End If

Next i

Cells(6, j) = somofdays

Next j

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

snb
08-27-2013, 11:49 AM
Please turn off 'caps lock' when entering a thread title.
Can you adapt the thread title into lowercase ?

Dharani
09-02-2013, 12:54 AM
You can do it this way too: In your workbook, access the VBE (alt+F11), create a new module and paste this:Sub countdays()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim i As Integer
Dim j As Integer
Dim somofdays As Integer

Application.Calculation = xlCalculationAutomatic
End Sub



Dear Salmogomes,
Thank you so much for the quick response and my heartfelt thanks to you... thank you for the effort you made...

Thanks & Regards,
Dharani.

Dharani
09-02-2013, 12:56 AM
It was really nice excersice for me.
Please check code below:
Goto VBE widow and add New module and use this code.


Option Explicit
Sub count_temp_days()
Dim N As Integer, Temp As Byte

N = N + 1
Wend
End Sub



Dear Raj85,
Thank you so much for the quick response and my heartfelt thanks to you... thank you for the effort you made...

Thanks & Regards,
Dharani.

Dharani
09-02-2013, 12:56 AM
Please turn off 'caps lock' when entering a thread title.
Can you adapt the thread title into lowercase ?


ok done sir...

Dharani
09-02-2013, 12:57 AM
Dharani,

Please mark this thred as resolved if you are satisfy with resolution provided.


yes sir I will do that... thank you sir.....

Dharani
09-02-2013, 12:59 AM
Why not use a formula?
F6=COUNTIF($C$2:$C$1923,">="&F5)

THANK YOU SIR....

salmogomes
09-02-2013, 08:06 AM
Dear Salmogomes, Thank you so much for the quick response and my heartfelt thanks to you... thank you for the effort you made... Thanks & Regards,Dharani.You're welcome. We're here to help each others.