Hi , guys I've got the code below, but I can't figure out where the compile error is. I thought I have covered all Do's with Loops.
Code:
Sub revampedloop()
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Dim i As Integer
Dim j As Integer
Dim a As Long
Dim b As Long
Dim myLastRow As Integer
i = 9 'Start Row
j = 3 'Z-score column
myLastRow = Range("A" & Rows.Count).End(xlUp).Row
'remove error'
For a = 1 To 2
On Error Resume Next
Cells(a, j).SpecialCells(xlCellTypeFormulas, 16).ClearContents
On Error GoTo 0
Next a
j = -1
j = j + 4
Do 'this is for whole sheet loop'
'remove the first wave of outlier'
i = 10
Do
If Cells(i, j - 1).Errors.Item(xlNumberAsText).value = False Then 'result NOT stored as text'
If (Abs(Cells(i, j).value) < 3) Then 'not outlier'
i = i + 1
ElseIf IsEmpty(Cells(i, j)) = True Then 'Skip blank cell'
i = i + 1
Else
Cells(i, j - 1).value = "'" & Cells(i, j - 1).value 'Result is now text'
i = 10 'restart at first row
End If
Else
i = i + 1
End If
Loop Until i > myLastRow 'loop for first column'
'**************************************************************************'
'2nd outlier cleanse'
j = 2 'starts at the 1st column'
i = 10
maxrow = 0
tempmax = 0
'start of max value for 1st column'
Do
maxrow = 0
tempmax = 0
Do
If Abs(Cells(i, j + 1).value) < 3 And Abs(Cells(i, j + 1).value) > tempmax Then
tempmax = Cells(i, j + 1).value
maxrow = i
i = i + 1
Else
i = i + 1
End If
Loop Until Cells(i, 1).value = ""
'end of max value for 1st column'
i = 10
'start'
If Cells(i, j).Errors.Item(xlNumberAsText).value = False Then 'result is a number'
tempvarR = Abs(Cells(i, j + 1).value) ' set temporary zscore'
tempvarL = Cells(i, j).value 'set temporary result'
If tempvarR = tempmax Then
tempvarL = "'" & Cells(i, j).value 'temp result is now a text'
If tempvarR >= 3 Then 'if zscore still outlier then perma it'
Cells(i, j + 1).value = tempvarR
Cells(i, j).value = tempvarL
Else
End If
Else
i = i + 1
End If
Else 'result is not a number'
i = i + 1
End If
'stop'
j = j + 4
i = 10
Loop Until Cells(9, j + 4).value = ""
'*************************************'
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
End Sub
