[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
11
Medium Priority
?
322 Views
Last Modified: 2010-04-17
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
Comment
Question by:ronaldj
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 31

Expert Comment

by:moorhouselondon
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

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

Expert Comment

by:RichieHindle
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 7

Accepted Solution

by:
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

If all fails, upgrade your hardware ;-)
0
 
LVL 7

Expert Comment

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

Author Comment

by:ronaldj
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

by:RichieHindle
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

by:Nievergelt
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

by:Nievergelt
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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

873 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