This code should be easier to understand.
I tried to use elementary instructions for a better clarity.
Sub Macro1()
Dim myCol As String, c As Integer
Dim myRow As Long, r As Long
Dim sourceSheetName As String
Dim monthNumber As Integer
Dim startRow As Long, endRow As Long
Dim myFormula As String
Dim myResult As Double
monthNumber = Range("a14")
'Since month data beginning from April (4)
'to get the correct destination row should be
'subtracted 4 from the the month value.
'The first row start at 3 and monthly rows step is 5 then
'to calculate startRow formula is:
startRow = (monthNumber - 4) * 5 + 3
endRow = startRow + 4
With ThisWorkbook.Sheets("summary")
For r = 6 To 8
sourceSheetName = Cells(r, "c")
For c = 4 To 10
'convert column number to letter:
myCol = Split(Columns(c - 1).Address(False, False), ":")(0)
myFormula = "average(" & sourceSheetName & "!" _
& myCol & startRow & ":" _
& myCol & endRow & ")" _
'if you want to put formula in sheet you can use code:
.Cells(r, c).Formula = "=" & myFormula
'if you prefer to use macro you can do:
If Not IsError(Evaluate(myFormula)) Then
'calculate formula value
myResult = Evaluate(myFormula)
.Cells(r, c) = myResult
Else
.Cells(r, c) = " - -"
End If
Next
Next
End With
End Sub
Regards,
Antonio
Bookmarks