[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

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

Posted on 2004-11-18
Medium Priority
322 Views
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
0
Question by:ronaldj
• 4
• 2
• 2
• +1

LVL 31

Expert Comment

ID: 12618848
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

LVL 31

Expert Comment

ID: 12618871
Sorry, after the line a=a+1 the value of b needs to be set to 0
0

LVL 14

Expert Comment

ID: 12618875
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

LVL 7

Accepted Solution

Nievergelt earned 1000 total points
ID: 12618938
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

LVL 7

Expert Comment

ID: 12618976
Sorry typo: I assume you do not want to change your worksheets.
0

Author Comment

ID: 12625201

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

LVL 14

Assisted Solution

RichieHindle earned 1000 total points
ID: 12625338
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

LVL 7

Expert Comment

ID: 12630695
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

LVL 7

Expert Comment

ID: 12858391
Hi ronaldj

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

Thanks  Christoph
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Make the most of your online learning experience.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress
###### Suggested Courses
Course of the Month19 days, 14 hours left to enroll