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

W

Warda

New Member
Joined
Apr 1, 2015
Messages
8
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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?
 
Upvote 0
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?
Click to expand...
 
Upvote 0
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
 
Upvote 0
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
Click to expand...
 
Upvote 0
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
 
Upvote 0
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
Click to expand...
 
Last edited:
Upvote 0
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)?
 
Upvote 0
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)?
Click to expand...
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Copy cell content based on the number of times it is repeated in a row
Replies
3
Views
191
Eric W
Eric W
E
  • Question
Dynamic small function
Replies
1
Views
196
KRice
K
H
  • Solved
How to make excel count based on time data?
Replies
3
Views
265
Fluff
Fluff
H
  • Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
153
harmless92
H
C
  • Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
1K
Trebor76
T

Forum statistics

Threads
1,214,994
Messages
6,122,633
Members
449,092
Latest member
bsb1122

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top