Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

Looping problem

Folks,
The code below is failing me. Here's my objective. In the Range D5:E12 if the value is "Correct" then I execute a module "FormulasOK". If a value <> "Correct" then I execute a module "CheckFormulaFunction".

Dim lRowLoop As Long

For lRowLoop = 5 To 12

 If Cells(lRowLoop, 4).Text = "Correct" Then
     Next lRowLoop
     Else
        CheckFormulaFunction
        Exit Sub
    End If

Open in new window

0
Frank Freese
Asked:
Frank Freese
  • 3
  • 2
3 Solutions
 
SteveCommented:
try:

Dim lRowLoop As Long
For lRowLoop = 5 To 12
 If Cells(lRowLoop, 4).value = "Correct" Then
        call FormulasOK
     Else
        call CheckFormulaFunction
    End If 
 Next lRowLoop

Open in new window

The above code only loops through D5:D12
If you want E5 to E12 you would repeat using Cells(lRowLoop, 5).value

you will need subs or functions called FormulasOK and CheckFormulaFunction
but this should sort out the code logic for you.
0
 
byundtCommented:
Did you mean to call FormulasOK only if all cells are Correct?
Sub FormulaChecker()
Dim lRowLoop As Long
For lRowLoop = 5 To 12
    If Cells(lRowLoop, 4).Text <> "Correct" Then
        CheckFormulaFunction
        Exit Sub
    End If
Next
FormulasOK
End Sub

Open in new window

0
 
SteveCommented:
Ahh, Brad, you may be right there, in that case I would tend to use a Boolean...

Sub FormulaChecker()
Dim lRowLoop As Long
Dim bCorrect as Boolean: bCorrect = True

For lRowLoop = 5 To 12
    If Cells(lRowLoop, 4).Value <> "Correct" Then bCorrect = False
Next
        
If bCorrect then
    FormulasOK
else
    CheckFormulaFunction
end if

End Sub

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Frank FreeseAuthor Commented:
I've changed Brad's suggestion some. As I read my changes below, what happens is that the first Loop checks for the "Correct" from 5 - 12 column 4. If there's not a "Correct" then CheckFormulaFunction is executed and I execute the sub.
The same thing happens in the seond Loop except it checks column 5. If everything is "Correct" then it executes FormulasOK. Is this new code correct then?

Dim lRowLoop As Long
For lRowLoop = 5 To 12
 If Cells(lRowLoop, 4).value = "Correct" Then
             Else
             CheckFormulaFunction
             Exit sub
    End If 
 Next lRowLoop

For lRowLoop = 5 To 12
 If Cells(lRowLoop, 5).value = "Correct" Then
             Else
             CheckFormulaFunction
             Exit Sub
    End If 
 Next lRowLoop

FormulasOK

Open in new window

0
 
Frank FreeseAuthor Commented:
The revised code I posted did what I was looking for.
With a few changes to Brads code I felt like he deserved the majority of the points.
But I am grateful for all that chimed in.
0
 
Frank FreeseAuthor Commented:
thanks everyone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now