Need help to shorten time it takes to execute...

I have the following code and it works just fine, problem is it takes 1 minute to execute. Can anyone think of a quicker approach?

For a = 1 To n1 + 2 Step 1 '# clients (141)
For b = 1 To n2 Step 1  '# categories (8)
For i = 1 To n3 '+ 1 Step 1 '# docs (577)

If Ws7.Cells(a + 1, 13).Value & Ws7.Cells(1, 13 + b).Value _
= Ws6.Cells(i + 1, 15).Value Then
Ws7.Cells(a + 1, 13 + b).Value = "YES"
End If

Thanks,
Ron
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
If you use a WHILE loop rather than STEP, then you can use a DONE flag to say when you have set Ws7.Cells(a + 1, 13 + b).Value to YES.

done=false
a=0
while not done and (a<=n1) do
a=a+1
while mot done and (b<=n2) do
b=b+1

...etc

Ws7.Cells(a + 1, 13 + b).Value="YES"
done=true

This way the loops will only execute fully if no condition set the value to YES
0
Commented:
Sorry, after the line a=a+1 the value of b needs to be set to 0
0
Commented:
You can speed it up by doing as many lookups as possible outside each loop.  Wherever something doesn't change within a loop, calculate it once outside the loop.

I'm not sure what language you're using, but it should look something like this:

Var Cells6 = Ws6.Cells
Var Cells7 = Ws7.Cells

For a = 1 To n1 + 2 Step 1 '# clients (141)
Var Cells7a = Cells7(a + 1, 13).Value
For b = 1 To n2 Step 1  '# categories (8)
Var Cells7b = Cells7(1, 13 + b).Value
Var Cells7ab = Cells7(a + 1, 13 + b)
For i = 1 To n3 '+ 1 Step 1 '# docs (577)
If Cells7a & Cells7b = Cells6(i + 1, 15).Value Then
Cells7ab.Value = "YES"
End If

...you'll be able to use more meaningful variable names than that, but you can see what I've done.  As you had it, for example, you were evaluating "Ws7.Cells(a + 1, 13).Value" 650997 times, but this calculates it only 141 times.
0
Senior SW DevCommented:
I assume you do not want to chane your worksheets.

The only way I see for improvement is to optimize the the cell access and the If statement:

Dim category As String, client As String, clientLen As Integer, docType As String

For a = 1 To n1 + 2 Step 1 '# clients (141)
client = Ws7.Cells(a + 1, 13).Value
clientLen = Len(client)
For b = 1 To n2 Step 1  '# categories (8)
category = Ws7.Cells(1, 13 + b).Value
For i = 1 To n3 '+ 1 Step 1 '# docs (577)
docType = Ws6.Cells(i + 1, 15).Value
If client = Left(docType, clientLen) Then
If client + category = docType Then
Ws7.Cells(a + 1, 13 + b).Value = "YES"
End If
Next i
Next b
Next a

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Senior SW DevCommented:
Sorry typo: I assume you do not want to change your worksheets.
0
Author Commented:

Nievergelt- thanks for the reply...except for the missing End IF, code ran fine...reduced execution from 65 seconds to 23...still wish i could get it closer to 15 seconds...

For a = 1 To n1 + 2 Step 1 '# clients (141)
client = Ws7.Cells(a + 1, 13).Value
clientLen = Len(client)
For b = 1 To n2 Step 1  '# categories (8)
category = Ws7.Cells(1, 13 + b).Value
For i = 1 To n3 '+ 1 Step 1 '# docs (577)
docType = Ws6.Cells(i + 1, 15).Value
If client = Left(docType, clientLen) Then
If client + category = docType Then
Ws7.Cells(a + 1, 13 + b).Value = "YES"
End If
End If
Next i
Next b
Next a
*********************************
RichieHindle - also thanks for the reply...needed to adjust your code a bit to be "vba" compliant...ran in about 30 seconds (actually, I thought this would have run quicker than the above)...

For a = 1 To n1 + 2 Step 1 '# clients (141)
client = Ws7.Cells(a + 1, 13).Value 'client
For b = 1 To n2 Step 1  '# categories (8)
category = Ws7.Cells(1, 13 + b).Value 'category
clientNcategory = Ws7.Cells(a + 1, 13 + b)
For i = 1 To n3 '+ 1 Step 1 '# docs (577)
If clientNcategory = Ws6.Cells(i + 1, 15).Value Then
Ws7.Cells(a + 1, 13 + b) = "YES"
End If
Next i
Next b
Next a
*********************************
I'd like to think about this a bit more, will award points before end of day. Thanks for everyone's help.

Ron
0
Commented:
You might be able to break out the Cells method lookups as well:

cells7 = Ws7.Cells
cells6 = Ws6.Cells
For a = 1 To n1 + 2 Step 1 '# clients (141)
client = cells7(a + 1, 13).Value 'client
...

It should make it a bit faster, but I doubt it will make a huge difference (and may not even be valid).
0
Senior SW DevCommented:
Hi Ron,

Sorry about the End If, but then in your code the Next statements were missing.
Never mind, it was just getting late (I live in Switzerland) and I (obviously) did no pay close attention.

As to why my code is slightly faster: It's in the If statements. By dividing the condition, the whole condition has only to be evaluated, if the first condition evaluates True.

Hey...., now I look at the code again, this might also work and will probably be faster:

For i = 1 To n3 '+ 1 Step 1 '# docs (577)
docType = Ws6.Cells(i + 1, 15).Value
For a = 1 To n1 + 2 Step 1 '# clients (141)
client = Ws7.Cells(a + 1, 13).Value
If client = Left(docType, Len(client)) Then ' test also categories
For b = 1 To n2 Step 1  '# categories (8)
If client + Ws7.Cells(1, 13 + b).Value = docType Then
Ws7.Cells(a + 1, 13 + b).Value = "YES"
End If
Next b
End If
Next a
Next i

I hope, I did not miss anything.

Regards  Christoph
0
Senior SW DevCommented:
Hi ronaldj

I would like you to tell us your final results.
Did you reach your magic 15 seconds?

Thanks  Christoph
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

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.