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]

While we are on the subject of speed.
I see a lot of people writing their code sort of like this:

Dim i as long
Dim b as long
Dim x as long
Dim pp as long

And on and on they might dim 25 different items.

The they do this:

Set Wk as Worksheet
Set aa as something

And they will set 25 more items

There first 50 lines of code are all about dimming and setting.


And then they only write 25 lines of code.

I dim most items maybe 5 or so

But I hardly ever set items.

I see no real value in setting Ws as Worksheet unless I'm going to use Worksheet 45 times in my code.

That's just my thought's so now tell me how wrong I am.



Does doing all this really speed up things significantly

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.

 

[MARK858]

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

Except that all Integers are stored as Long Integers these days so the usage of Integer is now claimed to be less efficient than using Long (although it would be a miracle to notice the difference).

I certainly very rarely find the declarations are the top a section of code in VBa help me to understand the code.

I would think the purpose of declaring the variable is to assign it's type so that Excel doesn't guess at it and not for understanding what is written in later code.
I declare variables mainly because of the rare occasion when Excel will assign the type incorrectly as it bases it's guess on the first usage of the variable and it is easier to debug using Option Explicit.

IMHO there is no reason to have what should be an Integer/Long or Double as Variant.
Variant is the least efficient data type as Excel has to guess at it's type. The data type should only be Variant when necessary (for example in arrays).

Btw, I don't see that the naming convention is relevant to whether or not you declare variables (I also use i, j & k for Integers/Longs btw but there are some programmers I know who who don't like it and prefer something nearer Hungarian notation so it is more descriptive).

Despite the above as far as I am concerned it is down to the individual whether they declare variables or not

Does doing all this really speed up things significantly

The straight answer is no

 

[offthelip]

I see a lot of people writing their code sort of like this:

I totally agree with you My Answer is this.
My feeling is if you are going to write 50 lines which do nothing or virtually nothing it is much better to write 50 lines of useful comments rather than 50 lines of declaration statements. Actually 10 lines of comments is usually enough
However I do agree with MARK858

Despite the above as far as I am concerned it is down to the individual whether they declare variables or not

I was just trying to point out to inexperienced users that declaring variables in not mandatory and experienced programmers might well opt not to use "option explicit"