Help optimizing a small piece of code (loop)

[ItalianPlatinum]

Hello,
I have a small piece of code that I put together but I am by no means a VBA expert and want to run it by someone with little more experience to see if it is the most efficient way to accomplish what I am trying to do. there is one thing i cannot modify (SecDist2 vba), current code works fine:

I run the secdist2 as many times for data listed in sheet Vs then that updates the parameters for the SecDist2 to run. so update to 1, run secdist2, update to 2, runsecdist2...etc.

Sub SDISALLV2()
Dim i%
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

ActiveSheet.AutoFilterMode = False

i = 1
With Sheets("Vs")
    Do Until .Range("VIEW").Offset(i, 0) = ""
    
    Sheets("Sec Dist").Range("SP_VIEW") = .Range("VIEW").Offset(i, 0)
    SecDist2
    
    i = i + 1
    Loop

End With

Sheets("Sec Dist").Range("SP0_VIEW") = 1

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

End Sub

 

[mmhill]

This loop does't do much, so I don't see a ton you can do to speed it up ... guessing SecDist2 is where all the time is spent.
There are a few things that might help a lttle bit, they're in the code.

It looks like you are using SP_VIEW to show the user how the loop is progressing. Writing to/from a sheet takes time and is much slower than using code alone. You might consider a small userform with a progressbar. I suspect that might be a little quicker but, depending on how long SecDist2 takes, you might not notice the difference.

Sub SDISALLV2()
Dim i%
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ActiveSheet.AutoFilterMode = False

Dim rngSP_VIEW As Range  '<<< USE AN OBJECT VARIABLE
Set rngSP_VIEW = Sheets("Sec Dist").Range("SP_VIEW")

i = 1
With Sheets("Vs").Range("VIEW") '<< STEP UP YOUR "WITH" GAME ... use object variable rngSP_VIEW here, too. 
    Do Until .Offset(i, 0) = ""
        rngSP_VIEW = .Offset(i, 0) 
        SecDist2
        i = i + 1 
    Loop
End With
Sheets("Sec Dist").Range("SP0_VIEW") = 1

Calculate  '<<<< EXPLICIT. MAY HELP. DON'T KNOW FOR SURE.

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[ItalianPlatinum]

Thank you - for sure I dont use with enough as I should. I will give this a go

 

[ItalianPlatinum]

Have one issue somehow the way you provides is looping through 3x? So i have 3 records of each and it actually ran the same time as my old one with 1 record which would in itself make it faster just not sure why its looping through additional times

 

[ItalianPlatinum]

ignore me user error my side

 

[mmhill]

I'm not sure what is happening. I took the Original Loop and simplified it ... got rid of the WITH statements and the SecDist2 and i = i + 1 lines. Then I simplified the NEW Loop. The simplified loops are identical. So, if the original loop worked and the new loop makes 3 passes, something is going on that I can't see.

'ORIGINAL LOOP
With Sheets("Vs")
    Do Until .Range("VIEW").Offset(i, 0) = ""    
        Sheets("Sec Dist").Range("SP_VIEW") = .Range("VIEW").Offset(i, 0)
    Loop
End With

'ORIGINAL SIMPLIFIED
    Do Until Sheets("Vs").Range("VIEW").Offset(i, 0) = ""    
        Sheets("Sec Dist").Range("SP_VIEW") = Sheets("Vs").Range("VIEW").Offset(i, 0)        
    Loop

'NEW LOOP
Set rngSP_VIEW = Sheets("Sec Dist").Range("SP_VIEW")
With Sheets("Vs").Range("VIEW")
    Do Until .Offset(i, 0) = ""    
        rngSP_VIEW = .Offset(i, 0)
    Loop    
End With

'NEW SIMPLIFIED
    Do Until Sheets("Vs").Range("VIEW").Offset(i, 0) = ""    
        Sheets("Sec Dist").Range("SP_VIEW") = Sheets("Vs").Range("VIEW").Offset(i, 0)
    Loop

 

[ItalianPlatinum]

I'm not sure what is happening. I took the Original Loop and simplified it ... got rid of the WITH statements and the SecDist2 and i = i + 1 lines. Then I simplified the NEW Loop. The simplified loops are identical. So, if the original loop worked and the new loop makes 3 passes, something is going on that I can't see.

'ORIGINAL LOOP
With Sheets("Vs")
    Do Until .Range("VIEW").Offset(i, 0) = ""  
        Sheets("Sec Dist").Range("SP_VIEW") = .Range("VIEW").Offset(i, 0)
    Loop
End With

'ORIGINAL SIMPLIFIED
    Do Until Sheets("Vs").Range("VIEW").Offset(i, 0) = ""  
        Sheets("Sec Dist").Range("SP_VIEW") = Sheets("Vs").Range("VIEW").Offset(i, 0)      
    Loop

'NEW LOOP
Set rngSP_VIEW = Sheets("Sec Dist").Range("SP_VIEW")
With Sheets("Vs").Range("VIEW")
    Do Until .Offset(i, 0) = ""  
        rngSP_VIEW = .Offset(i, 0)
    Loop  
End With

'NEW SIMPLIFIED
    Do Until Sheets("Vs").Range("VIEW").Offset(i, 0) = ""  
        Sheets("Sec Dist").Range("SP_VIEW") = Sheets("Vs").Range("VIEW").Offset(i, 0)
    Loop

it is all good see post #5. I changed the naming of the ranges and flipped them on accident. we are all set. thanks