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
ronaldjAsked:
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.

moorhouselondonCommented:
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
moorhouselondonCommented:
Sorry, after the line a=a+1 the value of b needs to be set to 0
0
RichieHindleCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NievergeltSenior 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

If all fails, upgrade your hardware ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
NievergeltSenior SW DevCommented:
Sorry typo: I assume you do not want to change your worksheets.
0
ronaldjAuthor 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
RichieHindleCommented:
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
NievergeltSenior 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
NievergeltSenior 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.