Help to make code run faster

[sbv1986]

Hi all:

I have this code belove to find and copy data then caculate value, this code work fine but I thinks it's very slow.
Please help me make code run faster a have another way to do (find data, copy then cacutale)

Thanks./.

Sub t_All()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet
Dim r As Integer
Set sh1 = Sheets("Data_new")
Set sh2 = Sheets("higher")
Set sh3 = Sheets("lower")
Set sh4 = Sheets("data_old")
r = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
rr = sh3.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
sh2.Cells.Clear
sh3.Cells.Clear
Application.ScreenUpdating = False
    With sh1
        For i = 3 To .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
            For j = 21 To .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
                If .Cells(i, "M").Value + .Cells(i, "N").Value < .Cells(i, j).Value Then
                    sh2.Cells(Rows.Count, 1).End(xlUp)(2) = .Cells(i, 3).Value
                    sh2.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = .Cells(2, j).Value
                    sh2.Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = .Cells(i, j).Value
                ElseIf .Cells(i, "M").Value - .Cells(i, "N").Value > .Cells(i, j).Value Then
                    sh3.Cells(Rows.Count, 1).End(xlUp)(2) = .Cells(i, 3).Value
                    sh3.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = .Cells(2, j).Value
                    sh3.Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = .Cells(i, j).Value
                End If
            Next
        Next
    End With
    With sh4
        For x = 3 To .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
            For y = 21 To .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
               For ii = 2 To r
                If sh2.Cells(ii, "A").Value = .Cells(x, "C").Value And sh2.Cells(ii, "B").Value = .Cells(2, y) Then
                sh2.Cells(ii, "D").Value = .Cells(x, y).Value
                End If
               Next
               For ii = 2 To rr
                If sh3.Cells(ii, "A").Value = .Cells(x, "C").Value And sh3.Cells(ii, "B").Value = .Cells(2, y) Then
                sh3.Cells(ii, "D").Value = .Cells(x, y).Value
                End If
               Next
            Next
        Next
    End With
    With sh2
        For i = 2 To .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        .Cells(i, "E").Value = .Cells(i, "C").Value - .Cells(i, "D").Value
         If .Cells(i, "D").Value = 0 Then
         .Cells(i, "F").Value = 1
         Else
         .Cells(i, "F").Value = (.Cells(i, "E") / .Cells(i, "D"))
         End If
        Next
        .Columns("F").NumberFormat = "#,###.##%"
        .Columns("C:E").NumberFormat = "#,###"
    End With
    With sh3
        For i = 2 To .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        .Cells(i, "E").Value = .Cells(i, "C").Value - .Cells(i, "D").Value
         If .Cells(i, "D").Value = 0 Then
         .Cells(i, "F").Value = 1
         Else
         .Cells(i, "F").Value = (.Cells(i, "E") / .Cells(i, "D"))
        End If
        Next
        .Columns("F").NumberFormat = "#,###.##%"
        .Columns("C:E").NumberFormat = "#,###"
    End With
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I always like it when users explain in words what their attempting to do.

Trying to read all this code with no ideal what it's trying to do is difficult for me.

Can you explain in detail what your attempting to do with this code.

And define slow.
Do you mean this code takes 1.5 seconds and you refer to this as slow?
Or does this script take 30 seconds?

 

[shirley999]

My VBA is very basic. Have you forgotten to declare rr?

 

[sbv1986]

My VBA is very basic. Have you forgotten to declare rr?

Maybe, and I forgotten dim i, ii and j too. But code still run, very amazing for me

 

[SpillerBD]

Maybe, and I forgotten dim i, ii and j too. But code still run, very amazing for me

I think the DIM would only diminish the Memory allocation for the variable.
Speeding up that code would be best to load the data into memory and work in in an Array and then lad the results back to the sheet.
Running those loops (on how many rows?) is just a lot of iterations

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...-if-conditions-are-meet-thiss-new-thread.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[shirley999]

I checked my facts.

Please try declaring your variables and let us know, does this make your macro run faster?

Declaring your variables makes your macro run faster and use memory more efficiently.

 

[offthelip]

Declaring the variables will have an insignificant effect on the speed of the macro. The reason this code is slow is because it make accesses the worksheet thousands of times ,
One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action.
So instead of writing a loop which loops down a range copying one row at a time which will take along time if you have got 50000 rows it is much quicker to load the 50000 lines into a variant array ( one worksheet access), then copy the lines to a variant array and then write the array back to the worksheet, ( one worksheet access for each search that you are doing),
I have a simple rule for fast VBA: NEVER ACCESS THE WORKSHEET IN A LOOP.
Comparte these two codes one loop through the worksheet operating on the cells and the othere does by loading the worksheet into a variant array and operating on the array. It is about 1000 times faster. Yes 1000 times!!! Note the extra 1 to 500 loop in the fast routine.
As suggested by SpillerBd above you need to use varaint array and get rid of all the accessto cells in the loops

Sub slow()
tt = Timer()
'initialise
 For j = 1 To 10
  Cells(j, 1) = 0
 Next j
For i = 1 To 1000
 For j = 1 To 10
  Cells(j, 1) = Cells(j, 1) + 1
 Next j
Next i
MsgBox (Timer() - tt)


End Sub


Sub fast()
tt = Timer()
Dim outarr(1 To 10, 1 To 1)
For k = 1 To 500
'initialise
 For j = 1 To 10
  outarr(j, 1) = 0
 Next j
Range(Cells(1, 1), Cells(10, 1)) = outarr


inarr = Range(Cells(1, 1), Cells(10, 1))
For i = 1 To 1000
 For j = 1 To 10
  inarr(j, 1) = inarr(j, 1) + 1
 Next j
Next i




Range(Cells(1, 1), Cells(10, 1)) = inarr
Next k
MsgBox (Timer() - tt)


End Sub

P.S. I never declare variables unless it is absolutely necessary. I prefer to use sensible names for the variables and write clear code.

 

[MARK858]

P.S. I never declare variables unless it is absolutely necessary. I prefer to use sensible names for the variables and write clear code.

You can do all three.... it is allowed

 

[offthelip]

I agree it is allowed but not necessary for clear code.
One of my compliants about VBA is that declaration types are very limited and any variable being written to or from the worksheet must be Variant type, which conveys no information about the variable.
So why waste time and space declaring things as variant, it is the default type anyway!!
In my code above I have got a number of counters i,j, and k, which actually could be type long, but that doesn't convey the fact that these counters must integers. They are also of type variant, but I am using a naming convention that I learnt about 50 years ago of using names starting i , j, k for integers. It is fairly obvious from the code what they are.
I certainly very rarely find the declarations are the top a section of code in VBa help me to understand the code.
Note I used to be an ADA programmer a long time ago , where declarations really meant something.