Speed up an Excel Macro?
- by N. Lucas
Right now I have a macro PopulateYearlyValues But it seems to me it's taking way too long
Sub PopulateYearlyValues(ByVal Month As Range)
Dim c As Double
Dim s As Double
c = Application.WorksheetFunction.Match(UCase(Month.Value), ActiveSheet.Range("AA5:AX5"), 0)
s = (ActiveSheet.Range("AA5").Column - 1)
With ActiveSheet
Dim i As Integer
Dim j As Integer
For i = 7 To 44
.Range("G" & i).Value = 0
.Range("H" & i).Value = 0
For j = 1 To c
.Range("G" & i).Value = (.Range("G" & i).Value + .Cells(i, s).Offset(0, j))
.Range("H" & i).Value = (.Range("H" & i).Value + .Cells(i, s).Offset(0, (j + 1)))
j = j + 1
Next j
Next i
End With
End Sub
I have a range G7:H44 that needs to be populated with the SUM of range AA7:AX44 but.. it's only every other column:
If Month.Value = "January"
G7 = SUM(AA7)
H7 = SUM(AB7)
...
G44 = SUM(AA44)
H44 = SUM(AB44)
End If
If Month.Value = "April"
G7 = SUM(AA7, AC7, AE7, AG7)
H7 = SUM(AB7, AD7, AF7, AH7)
...
G44 = SUM(AA44, AC44, AE44, AG44)
H44 = SUM(AB44, AD44, AF44, AH44)
End If
But the macro I have is taking way too long.. Is there any other way to do this?
