Help with vba code to find a number how many times repeated

[Warda]

Hallo Guys,

I am working on analysing data for over 277 sheets with different datas. What I am searching for is to find how many times is this number which is greater or eqaul to a number and less than another number repeated.

For example a number : 0<= number <4

I attached example worksheet to the following link to show what I mean.

https://www.dropbox.com/s/7ny4szcho9...piel.xlsx?dl=0

Many thanks for the help

 

[Warda]

Many many thanks for your help.

It did the job greatly.

Hello,

try this:

Private Sub CommandButton2_Click()
With Sheets("Wind_Data")
    For MY_ROWS = 1 To .Range("D" & Rows.Count).End(xlUp).Row
        Select Case .Range("D" & MY_ROWS).Value
            Case Is < 3
                THREE_LESS = THREE_LESS + 1
            Case Is < 3.5
                THREE_FIVE_LESS = THREE_FIVE_LESS + 1
            Case Is < 5
                FIVE_LESS = FIVE_LESS + 1
            Case Is < 5.5
                FIVE_FIVE_LESS = FIVE_FIVE_LESS + 1
            Case Is < 6
                SIX_LESS = SIX_LESS + 1
            Case Is < 6.5
                SIX_FIVE_LESS = SIX_FIVE_LESS + 1
            Case Is < 7
                SEVEN_LESS = SEVEN_LESS + 1
            Case Is < 7.5
                SEVEN_FIVE_LESS = SEVEN_FIVE_LESS + 1
            Case Is < 8
                EIGHT_LESS = EIGHT_LESS + 1
            Case Is < 8.5
                EIGHT_FIVE_LESS = EIGHT_FIVE_LESS + 1
            Case Is < 9
                NINE_LESS = NINE_LESS + 1
            Case Is < 9.5
                NINE_FIVE_LESS = NINE_FIVE_LESS + 1
            Case Is < 10
                TEN_LESS = TEN_LESS + 1
            Case Is < 10.5
                TEN_FIVE_LESS = TEN_FIVE_LESS + 1
            Case Is < 11
                ELEVEN_LESS = ELEVEN_LESS + 1
            Case Is < 11.5
                ELEVEN_FIVE_LESS = ELEVEN_FIVE_LESS + 1
            Case Is < 12
                TWELVE_LESS = TWELVE_LESS + 1
            Case Is < 13
                THIRTEEN_LESS = THIRTEEN_LESS + 1
            Case Is < 14
                FOURTEEN_LESS = FOURTEEN_LESS + 1
            Case Is < 20
                TWENTY_LESS = TWENTY_LESS + 1
            Case Is < 25
                TWENTY_FIVE_LESS = TWENTY_FIVE_LESS + 1
            Case Is < 30
                THIRTY_LESS = THIRTY_LESS + 1
            Case Is < 35
                THIRTY_FIVE_LESS = THIRTY_FIVE_LESS + 1
        End Select
    Next MY_ROWS
End With
With Sheets("Overall_Sum_Wind")
    .Range("A1").Value = "WIND SPEED"
    .Range("B1").Value = "HOURS"
    .Range("A2").Value = "0<= WG <3"
    .Range("B2").Value = THREE_LESS
    .Range("A3").Value = "3<= WG <3,5"
    .Range("B3").Value = THREE_FIVE_LESS
    .Range("A4").Value = "3,5<= WG <5"
    .Range("B4").Value = FIVE_LESS
    .Range("A5").Value = "5<= WG <5,5"
    .Range("B5").Value = FIVE_FIVE_LESS
    .Range("A6").Value = "5,5<= WG <6"
    .Range("B6").Value = SIX_LESS
    .Range("A7").Value = "6<= WG <6,5"
    .Range("B7").Value = SIX_FIVE_LESS
    .Range("A8").Value = "6,5<= WG <7"
    .Range("B8").Value = SEVEN_LESS
    .Range("A9").Value = "7<= WG <7,5"
    .Range("B9").Value = SEVEN_FIVE_LESS
    .Range("A10").Value = "7,5<= WG <8"
    .Range("B10").Value = EIGHT_LESS
    .Range("A11").Value = "8<= WG <8,5"
    .Range("B11").Value = EIGHT_FIVE_LESS
    .Range("A12").Value = "8,5<= WG <9"
    .Range("B12").Value = NINE_LESS
    .Range("A13").Value = "9<= WG <9,5"
    .Range("B13").Value = NINE_FIVE_LESS
    .Range("A14").Value = "9,5<= WG <10"
    .Range("B14").Value = TEN_LESS
    .Range("A15").Value = "10<= WG <10,5"
    .Range("B15").Value = TEN_FIVE_LESS
    .Range("A16").Value = "10,5<= WG <11"
    .Range("B16").Value = ELEVEN_LESS
    .Range("A17").Value = "11<= WG <11,5"
    .Range("B17").Value = ELEVEN_FIVE_LESS
    .Range("A18").Value = "11,5<= WG <12"
    .Range("B18").Value = TWELVE_LESS
    .Range("A19").Value = "12<= WG <13"
    .Range("B19").Value = THIRTEEN_LESS
    .Range("A20").Value = "13<= WG <14"
    .Range("B20").Value = FOURTEEN_LESS
    .Range("A21").Value = "14<= WG <20"
    .Range("B21").Value = TWENTY_LESS
    .Range("A22").Value = "20<= WG <25"
    .Range("B22").Value = TWENTY_FIVE_LESS
    .Range("A23").Value = "25<= WG <30"
    .Range("B23").Value = THIRTY_LESS
    .Range("A24").Value = "30<= WG <35"
    .Range("B24").Value = THIRTY_FIVE_LESS
    End With
End Sub

However, you can achieve the same results with a formula:


If, on the Overall_Sum_Wind sheet you can enter 3 in cell C2, 3.5 in cell C3, followed by all the < values, then in B2 enter this formula:

=COUNTIF(Wind_Data!D:D,"<"&C2)-SUM($B$1:B1)

and copy down as far as required.