RE: Multiple Arrays (Speeding things up a bit)

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
RE: Multiple Arrays (Speeding things up a bit)

Greetings,

Nice to see the forum back up and running. . .

I have code that runs through multiple arrays trying to match data to items in the arrays and it takes a long time to run. I was looking for some insight on speeding things up a bit.

Code is similar to the following:


Code:
dim a as long
dim b as long
dim c as long
dim d as long
dim e as long


dim MyAarray as variant
dim MyBarray as variant
dim MyCarray as variant
dim MyDarray as variant
dim MyEarray as variant


for a=1 to ubound(myaarray)
     for b=1 to ubound(mybarray)
          for c=1 to ubound(mycarray)
               for d=1 to ubound(mydarray)
                       for e =1 to ubound(myearray)

                        if cells(?,?).value=myarray(a,1) etc. . . . (for all arrays) then
                        'copies data to a workbook
                        end if
                      next e
                 next d
         next c
        next b
next a


that's basically what the code does. however, it takes an extremely long time to get through with everything as each array increases in size.

PLEASE HELP
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

AMAS

Active Member
Joined
Apr 11, 2010
Messages
472
Re: Multiple Arrays (Speeding things up a bit)

In this case, I would paste the data into an array and only print the array back to the sheet when all the checking is complete.

Also I would assign the value of cells(?,?) to a variable since it won't have to check the spreedsheet each time for the value since it will already be in memory.
 

Albatros

New Member
Joined
Aug 31, 2011
Messages
26
Re: Multiple Arrays (Speeding things up a bit)

Greetings,

Nice to see the forum back up and running. . .

I have code that runs through multiple arrays trying to match data to items in the arrays and it takes a long time to run. I was looking for some insight on speeding things up a bit.

Code is similar to the following:


Code:
dim a as long
dim b as long
dim c as long
dim d as long
dim e as long


dim MyAarray as variant
dim MyBarray as variant
dim MyCarray as variant
dim MyDarray as variant
dim MyEarray as variant


for a=1 to ubound(myaarray)
     for b=1 to ubound(mybarray)
          for c=1 to ubound(mycarray)
               for d=1 to ubound(mydarray)
                       for e =1 to ubound(myearray)

                        if cells(?,?).value=myarray(a,1) etc. . . . (for all arrays) then
                        'copies data to a workbook
                        end if
                      next e
                 next d
         next c
        next b
next a


that's basically what the code does. however, it takes an extremely long time to get through with everything as each array increases in size.

PLEASE HELP

Why do you use nested loops? If am not missing anything you are testing for the same condition redundantly.

for a =1 to ubound(myaarray)

if cells(?,?).value=myarray(a,1) etc. . . . then
'copies data to a workbook
end if
next a

for b =1 to ubound(mybarray)

if cells(?,?).value=mybarray(b,1) then
'copies data to a workbook
end if
next b

.....and so on.
 

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
Re: Multiple Arrays (Speeding things up a bit)

In this case, I would paste the data into an array and only print the array back to the sheet when all the checking is complete.

Also I would assign the value of cells(?,?) to a variable since it won't have to check the spreedsheet each time for the value since it will already be in memory.

amas thanks for the reply. . .

when you say paste into an array would Range("MyData")=MyAArray suffice????? and i'm using a variable for destrow (destination row) and the column.

Albatros. . . my purpose for using nested for next loops is because of the following


the data needs to sift through information that may apply

for example MyAarray="Dates" and on any given day "b" "c" "d" "e" can have all sorts of things take place. . .

is there a more efficient method in your eyes?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Re: Multiple Arrays (Speeding things up a bit)

Code:
dim a as long
dim b as long
dim c as long
dim d as long
dim e as long


dim MyAarray as variant
dim MyBarray as variant
dim MyCarray as variant
dim MyDarray as variant
dim MyEarray as variant


for a=1 to ubound(myaarray)
     for b=1 to ubound(mybarray)
          for c=1 to ubound(mycarray)
               for d=1 to ubound(mydarray)
                       for e =1 to ubound(myearray)

              [COLOR=#b22222][SIZE=3][B]if cells(?,?).value=myarray(a,1) etc. . . . (for all arrays) then[/B][/SIZE][/COLOR]
                        'copies data to a workbook
                        end if
                      next e
                 next d
         next c
        next b
next a
Could you provide a more detailed code line for the one I highlighted in red? What are the question marks standing in for? What does the "etc." look like? Quite frankly, I am having trouble figuring out what your code is doing or how the a, b, c, d and e For..Next loop variables fit into it.
 

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
Re: Multiple Arrays (Speeding things up a bit)

Code:
    For i = 1 To UBound(myArray)
    
    
    
        Counter = Counter + 1
        
    
        For c = 1 To UBound(MyCArray)
        
            For PN = 1 To UBound(MYPNArray)
        
                For j = 1 To UBound(MyRArray)

        
                    For p = 1 To UBound(MyJArray)
                
                        For o = 1 To UBound(MyOArray) 
                    
                     
                    
                           cntMemoHrs = 0
                            cntUnits = 0
                            
                             If destrow = 36 Then 'this code is added to change the sheet in the event data reaches the last row
                               destrow = 9
                               s = s + 1
                               End If
                             
                             For m = 1 To UBound(MyMArray) + 1
                             
                             
                                                          
                              If destrow = 36 Then 'this code is added to change the sheet in the event data reaches the last row
                               destrow = 9
                               s = s + 1
                               End If
                              
                                 If Cells(m, 2).Value = myArray(i, 1) And Cells(m, 9).Value = MyCArray(c, 1) And Cells(m, 3) = MyOArray(o, 1) And Cells(m, 4).Value = MyJArray(p, 1) And Cells(m, 8).Value = MyRArray(j, 1) And Cells(m, 12).Value = MYPNArray(PN, 1) Then
                                    If MyRArray(j, 1) = 13.89 Or MyRArray(j, 1) = 125 Or MyRArray(j, 1) > 30 Or MyRArray(j, 1) = 115 Then
                                    
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 6).Value = Cells(m, 3).Value 
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 7).Value = Cells(m, 4).Value 
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 3).Value = myArray(i, 1) 
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 4).Value = MyCArray(c, 1) 
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 5).Value = Cells(m, 12).Value   
                                     
                                         If Cells(m, 8).Value = 125 Or Cells(m, 8).Value = 115 Then 'adds code if the value is 125
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = Cells(m, 6).Value
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 10).Value = (Cells(m, 6).Value - 10) * -1
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = 1
                                         Else
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = Cells(m, 7).Value
                                         
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = Cells(m, 7).Value
                                         End If
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 12).Value = Cells(m, 8).Value 
                                     
                                    destrow = destrow + 1
                                    Else
                                     cntUnits = cntUnits + Cells(m, 7).Value
                                        cntMemoHrs = cntMemoHrs + Cells(m, 6).Value
                                    End If
                                    
                                                                   
                                   
                                End If
                 
                        Next m

                                        
                                 
                                 If cntUnits = 0 Then
                                 GoTo ResetMe
                                 
                                 Else
                                 
            

                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 7).Value = MyJArray(p, 1)
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 6).Value = "'" & MyOArray(o, 1) 
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 3).Value = myArray(i, 1) 
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 4).Value = MyCArray(c, 1)
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 5).Value = MYPNArray(PN, 1) 
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = "" 
                                    If MyRArray(j, 1) >= 8 Then
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = cntUnits
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = cntUnits
                                    Else
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = cntMemoHrs
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 10).Value = cntUnits
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = cntUnits
                                    End If
                                
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 12).Value = MyRArray(j, 1)
                                
                                destrow = destrow + 1
                                End If
                                
ResetMe:
 
 
                         
                            
                       Next o
                   
                    Next p
                       
               Next j
                     
               Next PN
               
                        
            Next c
     destrow = destrow + 1
     PctDone = Counter / UBound(myArray)
     
     Call UpdateProgress(PctDone)
    
 Next i
 

Albatros

New Member
Joined
Aug 31, 2011
Messages
26
Re: Multiple Arrays (Speeding things up a bit)

First suggestion BIgTone. You are using your two dimensional arrays as one dimensional as you are only looping through one dimension (you are only using one variable). Instead, create just a two dimensional array (1 to NumberOfCurrentArrays, 1 to MaximumNumberofItems). Each of your current arrasy would fill one of the "big Array" rows, or columns if you prefer. Then with two nested loops and two variables, you can go through the whole set of values contained in the big array and do the required comparisons with cells values.
 

BIGTONE559

Active Member
Joined
Apr 20, 2011
Messages
336
Re: Multiple Arrays (Speeding things up a bit)

Albatros could you provide an example? a little bit more information would help me if you don't mind.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top