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

Warda · Apr 16, 2015

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

onlyadrafter · Apr 17, 2015

Hello,

I have assumed you have all sheets in one workbook, with one extra sheet at the right hand side of the tab list. Also assumed all your data to be checked/analysed is in column D.

Try this code

Code:

Sub SUMMING()
    For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count - 1
        With Sheets(MY_SHEETS)
            For MY_ROWS = 1 To .Range("D" & Rows.Count).End(xlUp).Row
                Select Case .Range("D" & MY_ROWS).Value
                    Case Is < 6
                        SIX_LESS = SIX_LESS + 1
                    Case Is < 8
                        EIGHT_LESS = EIGHT_LESS + 1
                End Select
            Next MY_ROWS
        End With
    Next MY_SHEETS
    With Sheets(ActiveWorkbook.Sheets.Count)
        .Range("A1").Value = "0<= WG  <6"
        .Range("A2").Value = SIX_LESS
        .Range("B1").Value = "6<= WG  <8"
        .Range("B2").Value = EIGHT_LESS
    End With
End Sub

Is this anywhere close to the results expected?

Warda · Apr 19, 2015

Dear onlyadrafter,

Thanks alot for your help and time but not exactly this what I was seeking for. What I ment is to search for the results and write them in a table as I showed in the example I posted in the link.

So to look for the values less than six but not to count them all together but to get them in seperated form because it is about studying the speed running over time. In other way if the speed is being less than 6 for 4 hours to put the value in one cell 4 and then if for 2 hours to put the value 2 in a new cell so in seperated cells and not the total number in one cell. I hope it is clear in case there is questions let me know.

Again thanks alot.

onlyadrafter said:

Hello,

I have assumed you have all sheets in one workbook, with one extra sheet at the right hand side of the tab list. Also assumed all your data to be checked/analysed is in column D.

Try this code

Code:

Sub SUMMING()
    For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count - 1
        With Sheets(MY_SHEETS)
            For MY_ROWS = 1 To .Range("D" & Rows.Count).End(xlUp).Row
                Select Case .Range("D" & MY_ROWS).Value
                    Case Is < 6
                        SIX_LESS = SIX_LESS + 1
                    Case Is < 8
                        EIGHT_LESS = EIGHT_LESS + 1
                End Select
            Next MY_ROWS
        End With
    Next MY_SHEETS
    With Sheets(ActiveWorkbook.Sheets.Count)
        .Range("A1").Value = "0<= WG  <6"
        .Range("A2").Value = SIX_LESS
        .Range("B1").Value = "6<= WG  <8"
        .Range("B2").Value = EIGHT_LESS
    End With
End Sub

Is this anywhere close to the results expected?

onlyadrafter · Apr 22, 2015

Hello,

I see.

Does this work as expected?

Code:

Sub COUNTING_TIMES()
    Application.ScreenUpdating = False
    MY_START = 2
    MY_COUNT = 1
    Do Until IsEmpty(Range("D" & MY_START + 1).Value)
        MY_NEXT_NO = Range("D" & MY_START).Value
        MY_NO = Range("D" & MY_START + 1).Value
        If MY_NEXT_NO <= 6 And MY_NO <= 6 _
             Or MY_NEXT_NO > 6 And MY_NEXT_NO < 8 And MY_NO > 6 And MY_NO < 8 Then
                MY_COUNT = MY_COUNT + 1
            Else
                If MY_NEXT_NO <= 6 Then
                    Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                Else
                    Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                End If
                MY_COUNT = 1
            
        End If
        MY_START = MY_START + 1
    Loop
    MY_START = MY_START + 1
    MY_COUNT = 1
    Application.ScreenUpdating = True
End Sub

Warda · Apr 23, 2015

Dear onlyadrafter,

Exactly that what I was looking for thanks a lot for your help. But Please if you could help me I need another favour :

In case I had more intervals for example
0<=WG<6
6<=WG<8
8<=WG<10
10<=WG<15
15<=WG<25

How can the code be modified I tried to modify it but it didn't work with me.

Again many thanks for your help and time.

onlyadrafter said:

Hello,

I see.

Does this work as expected?

Code:

Sub COUNTING_TIMES()
    Application.ScreenUpdating = False
    MY_START = 2
    MY_COUNT = 1
    Do Until IsEmpty(Range("D" & MY_START + 1).Value)
        MY_NEXT_NO = Range("D" & MY_START).Value
        MY_NO = Range("D" & MY_START + 1).Value
        If MY_NEXT_NO <= 6 And MY_NO <= 6 _
             Or MY_NEXT_NO > 6 And MY_NEXT_NO < 8 And MY_NO > 6 And MY_NO < 8 Then
                MY_COUNT = MY_COUNT + 1
            Else
                If MY_NEXT_NO <= 6 Then
                    Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                Else
                    Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                End If
                MY_COUNT = 1
            
        End If
        MY_START = MY_START + 1
    Loop
    MY_START = MY_START + 1
    MY_COUNT = 1
    Application.ScreenUpdating = True
End Sub

onlyadrafter · Apr 25, 2015

Hello,

Does this work?

Code:

Sub COUNTING_TIMES()
    Application.ScreenUpdating = False
    MY_START = 2
    MY_COUNT = 1
    Do Until IsEmpty(Range("D" & MY_START + 1).Value)
        MY_NEXT_NO = Range("D" & MY_START).Value
        MY_NO = Range("D" & MY_START + 1).Value
        If MY_NEXT_NO < 6 And MY_NO < 6 Or _
              MY_NEXT_NO >= 6 And MY_NEXT_NO < 8 And MY_NO >= 6 And MY_NO < 8 Or _
              MY_NEXT_NO >= 8 And MY_NEXT_NO < 10 And MY_NO >= 8 And MY_NO < 10 Or _
              MY_NEXT_NO >= 10 And MY_NEXT_NO < 15 And MY_NO >= 10 And MY_NO < 15 Or _
              MY_NEXT_NO >= 15 And MY_NEXT_NO < 25 And MY_NO >= 15 And MY_NO < 25 Then
                MY_COUNT = MY_COUNT + 1
            Else
                Select Case MY_NEXT_NO
                    Case Is < 6
                        Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 8
                        Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 10
                        Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 15
                        Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 25
                        Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                End Select
                MY_COUNT = 1
            
        End If
        MY_START = MY_START + 1
    Loop
    MY_START = MY_START + 1
    MY_COUNT = 1
    Application.ScreenUpdating = True
End Sub

Have assumed your headings in G4 and H4 go across to K4

Warda · Apr 26, 2015

Thanks alot that what I was looking for. Great it did what I was serching for.

I would like to ask for another favour regarding the first code you sent me. I want the code to sum the repeated number in one sheet. The code I modified is as the following but I have a problem in letting the macro to search just in one sheet the sheet name is (Wind_Data).

I tried to let the macro to search in one specific sheet but it didn't work for me so please if you can edit the following code to do the job.

Private Sub CommandButton2_Click()
For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count - 1
With Sheets(MY_SHEETS)
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
Next MY_SHEETS
With Sheets(ActiveWorkbook.Sheets.Count)
.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

onlyadrafter said:

Hello,

Does this work?

Code:

Sub COUNTING_TIMES()
    Application.ScreenUpdating = False
    MY_START = 2
    MY_COUNT = 1
    Do Until IsEmpty(Range("D" & MY_START + 1).Value)
        MY_NEXT_NO = Range("D" & MY_START).Value
        MY_NO = Range("D" & MY_START + 1).Value
        If MY_NEXT_NO < 6 And MY_NO < 6 Or _
              MY_NEXT_NO >= 6 And MY_NEXT_NO < 8 And MY_NO >= 6 And MY_NO < 8 Or _
              MY_NEXT_NO >= 8 And MY_NEXT_NO < 10 And MY_NO >= 8 And MY_NO < 10 Or _
              MY_NEXT_NO >= 10 And MY_NEXT_NO < 15 And MY_NO >= 10 And MY_NO < 15 Or _
              MY_NEXT_NO >= 15 And MY_NEXT_NO < 25 And MY_NO >= 15 And MY_NO < 25 Then
                MY_COUNT = MY_COUNT + 1
            Else
                Select Case MY_NEXT_NO
                    Case Is < 6
                        Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 8
                        Range("H" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 10
                        Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 15
                        Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                    Case Is < 25
                        Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Value = MY_COUNT
                End Select
                MY_COUNT = 1
            
        End If
        MY_START = MY_START + 1
    Loop
    MY_START = MY_START + 1
    MY_COUNT = 1
    Application.ScreenUpdating = True
End Sub

Have assumed your headings in G4 and H4 go across to K4

onlyadrafter · Apr 30, 2015

Hello,

Don't understand what you are trying to do. From your file what results do you expect from the first 5 cells in Col D (the yellow cells)?

Warda · May 3, 2015

Hallo,

The data are about speeds over time. These speeds are measured for the whole year and needed to run a turbine to produce power. So the data are analysed for different intervals and what is stated on the exmple sheet are just dummy data with some intervals.

Interveals that I am working on are starting from zero ending by 50 m/s . ex: [0-3[,[3-3,5[,[3,5-4],..... and so on.

The code you sent me last time did what I was lookin for.

But I would like to ask for another favour regarding the first code you sent me. I want the code to sum the repeated number in one sheet. The code I modified is as the following but I have a problem in letting the macro to search just in one sheet the sheet name is (Wind_Data). And after looking in this sheet I want the code to save the results into a sheet named by (Overall_Sum_Wind).

I tried to let the macro to search in one specific sheet but it didn't work for me so please if you can edit the following code to do the job.

Private Sub CommandButton2_Click()
For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count - 1
With Sheets(MY_SHEETS)
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
Next MY_SHEETS
With Sheets(ActiveWorkbook.Sheets.Count)
.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

onlyadrafter said:
Hello,

Don't understand what you are trying to do. From your file what results do you expect from the first 5 cells in Col D (the yellow cells)?

onlyadrafter · May 4, 2015

Hello,

try this:

Code:

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.

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

Warda

New Member

Excel Facts

onlyadrafter

Well-known Member

Warda

New Member

onlyadrafter

Well-known Member

Warda

New Member

onlyadrafter

Well-known Member

Warda

New Member

onlyadrafter

Well-known Member

Warda

New Member

onlyadrafter

Well-known Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock