good morning!
I'm trying to:
1 - Loop all my sheets, starting from the 2nd sheet (until here it's working);
2 - Find Max, Min Value and Interval (Max-Min Value/4), assign to cells, and define 3 more intervals iQ1, iQ2 and iQ3. This way I goot all the intervals I need to build 4 quantiles (until here it's working too);
3 - Now, in each sheet and in the same loop, I need to search in column F for all the values of the column that are <= iQ1 (and creater other conditions for other intervals (iQs)). If those values in the loop are <=Q1, for instance, I need to copy and paste all of them and their quantity (Column G) in the columns J2:J (for interest) and K2:K (for quantity). I create a picture to explain better.
I need this because I'll need to calculate the median of each quantile after.
I tried the first loop only for the column F to try, but it failed this and other things that I tried. Could you help me with item 3, please?
Thanks and have a great day!
Application.ScreenUpdating = False
Dim ws2 As Worksheet
Dim x As Long, Interval As Double, MaxValue As Double, MinValue As Double, iQ1 As Double, iQ2 As Double, iQ3 As Double, rw2 As Object
For x = 2 To Sheets.Count
Sheets(x).Activate
Dim c As Range
Set c = Range("F2:F" & Rows.Count)
MaxValue = Application.WorksheetFunction.Max(c)
MinValue = Application.WorksheetFunction.Min(c)
Interval = (MaxValue - MinValue) / 4
Sheets(x).Range("I2").Value = Interval
Sheets(x).Range("P2").Value = MaxValue
Sheets(x).Range("O2").Value = MinValue
Sheets(x).Range("J2:M500000").Clear
iQ1 = MinValue + Interval
iQ2 = iQ1 + Interval
iQ3 = iQ2 + Interval
For Each rw2 In Sheets(x).Range(c) 'Here is the loop that I'm stucked
If rw2.Cells(6).Value <= iQ1 Then 'Here is the condition blue for F, it's in the picture
With Sheets(x)
rw2.EntireRow.Copy
.Cells(.Rows.Count, "J2:J").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End With
End If
Next rw2
Next x
Application.ScreenUpdating = True