VBA Macro to generate a report of mean values for each date

Question

My Macro that I wrote is running really slowly and I'm not sure why. The Macro deletes the area, then reproduces one column of locations as a row, deleting any duplicates, then copy's across a column of dates, then puts the corresponding Mean value for each date under the correct functional location.

Sub Macro1()
'
' Macro1 Macro


' Delete Previous Data
Range("J1:AQ1000").Clear

' Functional Location

Dim toAdd As Boolean, uniqueTag As Integer, i As Integer, j As Integer
Cells(1, 11).Value = Cells(2, 2).Value
uniqueTag = 11
toAdd = True
For i = 3 To 1000
For j = 11 To uniqueTag
If Cells(i, 2).Value = Cells(1, j).Value Then
toAdd = False
End If
Next j
If toAdd = True Then
Cells(1, uniqueTag + 1).Value = Cells(i, 2).Value
uniqueTag = uniqueTag + 1
End If
toAdd = True
Next i

' Date

Dim k As Integer
For k = 2 To 1000
Cells(k, 10).Value = Cells(k, 6).Value
Next k

' Mean Value
Dim l As Integer, m As Integer
For l = 2 To 1000
For m = 11 To 100
If Cells(l, 2).Value = Cells(1, m).Value Then
Cells(l, m).Value = Cells(l, 5).Value
End If
Next m
Next l



End Sub

Are there any optimizations that would make it run faster, or something I haven't included? The first parts all work pretty speedily, its only the copying of the mean values which appears to take time.

you are iterating 100.000 times. If you reduce the number of iterations, the copying will be faster. Could you describe what the purpose of the copying is? Maybe you could copy more than one cell at a time via copying a range. — Sebastian Siemens
– Sebastian Siemens, Commented Mar 8, 2017 at 11:51

Sebastian Siemens · Accepted Answer · 2017-03-08 12:42:41Z

In my example the iteration needs 2 seconds. How long is your code running?

Note 1: Reduce the number of iterations. You are iterating 100.000 times

Note 2: You are accessing the same cell Cells(l, 2).Value very often in the second for loop. But the value does not change. It will change in the first for loop.

You can save the value in a local variable in the first loop:

Dim l2 As Variant
l2 = Cells(l, 2).Value

Note 3: You are accessing the same 90 cells Cells(1, m).Value very often too (1000 times).

You can save the 90 values in an array before looping:

Dim Arr() As Variant
Arr = Range("K1:CV1")

My complete code looks like this:

' Mean Value
Dim Arr() As Variant
Arr = Range("K1:CV1")
Dim l As Integer, m As Integer
For l = 2 To 1000
    Dim l2 As Variant
    l2 = Cells(l, 2).Value
    For m = 1 To 90
        If l2 = Arr(1, m) Then
            Cells(l, m).Value = Cells(l, 5).Value
        End If
    Next m
Next l

Stack Exchange Network

VBA Macro to generate a report of mean values for each date

1 Answer 1

You must log in to answer this question.

Hot Network Questions

VBA Macro to generate a report of mean values for each date

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions