PDA

View Full Version : [SOLVED] For and Next code is not being excecuted (if, else if)



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

mdmackillop
06-21-2017, 12:38 PM
If you can make 4 more posts in introductions/testing forums you should then be able to attach a workbook using Go Advanced/Manage Attachments

VanChester
06-21-2017, 12:43 PM
Hello :hi:and thank you for the reply. I have posted to the Introductions board. I will now attach the document. Thank you.

VanChester
06-22-2017, 11:49 AM
If you can make 4 more posts in introductions/testing forums you should then be able to attach a workbook using Go Advanced/Manage Attachments I cannot find how to edit my own post and attach the excel document.

mdmackillop
06-23-2017, 12:52 AM
Attach your workbook to your next reply.

VanChester
06-23-2017, 08:40 AM
Attach your workbook to your next reply.

1958619588

Hello, I have now added both the workbook, where I am having issues with the macros stated earlier, and test data.

Paul_Hossler
06-23-2017, 10:02 AM
As a minor observation, in VBA you need to spell out every Dim Something As ..... otherwise VBA makes it Variant type

So Rng1 to Rng11 are Variants, but Rng12 is a Range

And MNIVR to Ans are Variants



Dim Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10, Rng11, Rng12 As Range

Dim MNIVR, MMIVR, NMIVR, NNIVR, GLIVR, GLManual, MNManual, MMManual, NMManual, NNManual, EFMNIVR, _
EFMMIVR, EFNMIVR, EFNNIVR, EFGLIVR, EFGLManual, EFMNManual, EFMMManual, EFNMManual, EFNNManual, Ans, SumCount _
As Integer



Something like ...



Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, .... Rng12 As Range

VanChester
07-17-2017, 07:26 AM
As a minor observation, in VBA you need to spell out every Dim Something As ..... otherwise VBA makes it Variant type

So Rng1 to Rng11 are Variants, but Rng12 is a Range

And MNIVR to Ans are Variants



Dim Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, Rng7, Rng8, Rng9, Rng10, Rng11, Rng12 As Range

Dim MNIVR, MMIVR, NMIVR, NNIVR, GLIVR, GLManual, MNManual, MMManual, NMManual, NNManual, EFMNIVR, _
EFMMIVR, EFNMIVR, EFNNIVR, EFGLIVR, EFGLManual, EFMNManual, EFMMManual, EFNMManual, EFNNManual, Ans, SumCount _
As Integer



Something like ...



Dim Rng1 As Range, Rng2 As Range, Rng3 As Range, .... Rng12 As Range



Thank you for the observation. Turns out it helped. My code now works! Thank you!