Solved

Looping problem

Posted on 2013-10-22
6
127 Views
Last Modified: 2013-10-22
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
Comment
Question by:Frank Freese
  • 3
  • 2
6 Comments
 
LVL 24

Assisted Solution

by:Steve
Steve earned 100 total points
ID: 39591177
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
 
LVL 80

Accepted Solution

by:
byundt earned 400 total points
ID: 39591189
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
 
LVL 24

Assisted Solution

by:Steve
Steve earned 100 total points
ID: 39591212
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Frank Freese
ID: 39591341
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
 

Author Comment

by:Frank Freese
ID: 39591411
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
 

Author Closing Comment

by:Frank Freese
ID: 39591419
thanks everyone.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now