VanChester
06-21-2017, 10:35 AM
Hello, this is currently my code:
For Each Rng3 In Range("$A$3:$A$1000")
If Rng3.Value = "Account Credit (Debit G/L)" Then
If Rng3.Offset(0, 8).Value = "IVR Risk" Then
If Rng3.Offset(0, 1).Value = 0 Or Rng3.Offset(0, 1).Value > 0 Then
GLIVR = GLIVR + 1
Else: Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "No" And _
Rng3.Offset(0, 5).Value <> "812-519" Then
If Rng3.Offset(0, 1).Value = 0 Or Rng3.Offset(0, 1).Value > 0 Then
GLManual = GLManual + 1
ElseIf Rng3.Offset(0, 1).Value < 0 Then Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "Yes" And _
Rng3.Offset(0, 5).Value <> "812-519" Then
If Rng3.Offset(0, 1).Value = 0 Or Rng3.Offset(0, 1).Value > 0 Then
EFGLManual = EFGLManual + 1
ElseIf Rng3.Offset(0, 8).Value = "IVR Risk" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLIVR = GLIVR + 1
Else: Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "No" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLManual = GLManual + 1
ElseIf Rng3.Offset(0, 1).Value < 0 Then Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "Yes" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
EFGLManual = EFGLManual + 1
End If
End If
End If
End If
Next Rng3'
Although I do not get any error messages, this code is working partially. For the values under "812-519" the filtering works, but for the values under "812-135" It doesn't. The part of the code I have an issue executing is:
ElseIf Rng3.Offset(0, 8).Value = "IVR Risk" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLIVR = GLIVR + 1
Else: Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "No" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLManual = GLManual + 1
ElseIf Rng3.Offset(0, 1).Value < 0 Then Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "Yes" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
EFGLManual = EFGLManual + 1
End If
End If
End If
End If
Next Rng3
This part seems to do nothing to my results.
The idea behind this code is that I want to clear the rows that have values below 250 only when cells under column 5 is named "812-135".
If the value is 250 or above, I want it to show just like the above lines do. For "812-519" I want to show all values and the code works. But not for "812-135"
Basically I want to have the same results that are shown under "812-519" to be shown under "812-135"
but not for any value, only for values above or equal to 250.
Currently with this code it shows all values under "812-135" (for example 2,3,5,109,111 etc.).
Any help is greatly apreciated
For Each Rng3 In Range("$A$3:$A$1000")
If Rng3.Value = "Account Credit (Debit G/L)" Then
If Rng3.Offset(0, 8).Value = "IVR Risk" Then
If Rng3.Offset(0, 1).Value = 0 Or Rng3.Offset(0, 1).Value > 0 Then
GLIVR = GLIVR + 1
Else: Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "No" And _
Rng3.Offset(0, 5).Value <> "812-519" Then
If Rng3.Offset(0, 1).Value = 0 Or Rng3.Offset(0, 1).Value > 0 Then
GLManual = GLManual + 1
ElseIf Rng3.Offset(0, 1).Value < 0 Then Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "Yes" And _
Rng3.Offset(0, 5).Value <> "812-519" Then
If Rng3.Offset(0, 1).Value = 0 Or Rng3.Offset(0, 1).Value > 0 Then
EFGLManual = EFGLManual + 1
ElseIf Rng3.Offset(0, 8).Value = "IVR Risk" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLIVR = GLIVR + 1
Else: Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "No" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLManual = GLManual + 1
ElseIf Rng3.Offset(0, 1).Value < 0 Then Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "Yes" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
EFGLManual = EFGLManual + 1
End If
End If
End If
End If
Next Rng3'
Although I do not get any error messages, this code is working partially. For the values under "812-519" the filtering works, but for the values under "812-135" It doesn't. The part of the code I have an issue executing is:
ElseIf Rng3.Offset(0, 8).Value = "IVR Risk" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLIVR = GLIVR + 1
Else: Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "No" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
GLManual = GLManual + 1
ElseIf Rng3.Offset(0, 1).Value < 0 Then Rng3.ClearContents
End If
ElseIf Rng3.Offset(0, 8).Value = "Manual Risk" And Rng3.Offset(0, 9).Value = "Yes" And _
Rng3.Offset(0, 5).Value <> "812-135" Then
If Rng3.Offset(0, 1).Value = 250 Or Rng3.Offset(0, 1).Value > 250 Then
EFGLManual = EFGLManual + 1
End If
End If
End If
End If
Next Rng3
This part seems to do nothing to my results.
The idea behind this code is that I want to clear the rows that have values below 250 only when cells under column 5 is named "812-135".
If the value is 250 or above, I want it to show just like the above lines do. For "812-519" I want to show all values and the code works. But not for "812-135"
Basically I want to have the same results that are shown under "812-519" to be shown under "812-135"
but not for any value, only for values above or equal to 250.
Currently with this code it shows all values under "812-135" (for example 2,3,5,109,111 etc.).
Any help is greatly apreciated