You had nearly the correct structure, and hopefully the points below will help you keep things straight.
First, you can loop through all the sheets in your workbook a bit simpler with the sample here, including skipping a particular sheet if you need to:
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If Not ws.Name = "SKIP THIS SHEET" Then
With ws
...
End With
End If
Next ws
Using a loop like this, you can be assured that ws
as always the worksheet that is operated on. Notice the With
statement here and always make sure to preface your references to Range
or Cells
with the dot .
to make sure it's working on that ws
worksheet.
Next, it's good practice to declare your variables closer to the point where they are first used and to put each variable on its own line. This can be a personal preference of course, but it's currently the most common habit.
Where your inner loop is not working is how you're referencing the different data. In my example below, each of the Quartil ranges is defined clearly. Also, I'm using more descriptive variable names to indicate what data I'm currently working on. Finally, it was easier to break out a separate routine to append the interest data in a particular quartil, in order to show how common code sections can be isolated in a function/sub.
Option Explicit
Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If Not (ws.Name = "SKIP THIS SHEET") Then
With ws
Dim interestData As Range
Dim lastRow As Long
lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
Set interestData = .Range("F2:F" & lastRow)
Dim Interval As Double
Dim MaxValue As Double
Dim MinValue As Double
Dim iQ1 As Double
Dim iQ2 As Double
Dim iQ3 As Double
MaxValue = Application.WorksheetFunction.Max(interestData)
MinValue = Application.WorksheetFunction.Min(interestData)
Interval = (MaxValue - MinValue) / 4
.Range("I2").Value = Interval
.Range("R2").Value = MaxValue
.Range("S2").Value = MinValue
.Range("J2:Q500000").Clear
iQ1 = MinValue + Interval
iQ2 = iQ1 + Interval
iQ3 = iQ2 + Interval
Debug.Print "Quartil 1: <= " & Format(iQ1, "000.000")
Debug.Print "Quartil 2: > " & Format(iQ1, "000.000") & ", <= " & Format(iQ2, "000.000")
Debug.Print "Quartil 3: > " & Format(iQ2, "000.000") & ", <= " & Format(iQ3, "000.000")
Debug.Print "Quartil 4: => " & Format(iQ3, "000.000")
Dim q1 As Range
Dim q2 As Range
Dim q3 As Range
Dim q4 As Range
Set q1 = .Range("J2")
Set q2 = .Range("L2")
Set q3 = .Range("N2")
Set q4 = .Range("P2")
Dim interestValues As Variant
For Each interestValues In interestData
If (interestValues.Value <= iQ1) Then
AppendInterest q1, interestValues
ElseIf (interestValues.Value > iQ1) And (interestValues.Value <= iQ2) Then
AppendInterest q2, interestValues
ElseIf (interestValues.Value > iQ2) And (interestValues.Value <= iQ3) Then
AppendInterest q3, interestValues
Else 'interestValues > iQ3
AppendInterest q4, interestValues
End If
Next interestValues
End With
End If
Next ws
End Sub
Private Sub AppendInterest(ByRef quartil As Range, _
ByVal interest As Range)
'--- copies the data in to the first empty row of the
' quartil group
Dim lastRow As Long
With quartil.Parent 'this is the worksheet
lastRow = .Cells(.Rows.Count, quartil.Column).End(xlUp).Row
quartil.Cells(lastRow, 1).Value = interest.Cells(1, 1).Value 'interest
quartil.Cells(lastRow, 2).Value = interest.Cells(1, 2).Value 'qty
End With
End Sub
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…