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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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
Back
Top