db1
10-23-2012, 02:13 AM
Hi there,
I'm struggling to set a macro to calculate the average value over countries for different variables (i = 1 To 8). The % by country, by variable and by year is on sheet "output" and the average % by variable and by year should be in sheet "Average". Here is a bit of the code I need to work around.
Sub Average_AF()
'
Dim tabcode(1 To 8) As String
Dim Statavg_99 As Double
Dim Statavg_00 As Double
Dim Statavg_01 As Double
Dim Statavg_02 As Double
Dim Statavg_03 As Double
Dim Statavg_04 As Double
Dim Statavg_05 As Double
Dim Statavg_06 As Double
Dim Statavg_07 As Double
Dim Statavg_08 As Double
Dim l As Variant
Dim rng As Range
tabcode(1) = "00_"
tabcode(2) = "01_"
tabcode(3) = "02_"
tabcode(4) = "03_"
tabcode(5) = "04_"
tabcode(6) = "05_"
tabcode(7) = "06_"
tabcode(8) = "02B"
Sheets("Output").Select
Sheets.Add
' RENAME the new sheet
ActiveSheet.Name = "Average"
Myoutput = ActiveSheet.Name
Sheets("Output").Select
Set rng = Range("c3:m220")
Statavg_99 = Application.Average(rng.Columns(2))
Statavg_00 = Application.Average(rng.Columns(2))
Statavg_01 = Application.Average(rng.Columns(3))
Statavg_02 = Application.Average(rng.Columns(4))
Statavg_03 = Application.Average(rng.Columns(5))
Statavg_04 = Application.Average(rng.Columns(6))
Statavg_05 = Application.Average(rng.Columns(7))
Statavg_06 = Application.Average(rng.Columns(8))
Statavg_07 = Application.Average(rng.Columns(9))
Statavg_08 = Application.Average(rng.Columns(10))
For l = 3 To 9
Sheets(Myoutput).Cells(l, 2) = Statavg_99
Sheets(Myoutput).Cells(l, 3) = Statavg_00
Sheets(Myoutput).Cells(l, 4) = Statavg_01
Sheets(Myoutput).Cells(l, 5) = Statavg_02
Sheets(Myoutput).Cells(l, 6) = Statavg_03
Sheets(Myoutput).Cells(l, 7) = Statavg_04
Sheets(Myoutput).Cells(l, 8) = Statavg_05
Sheets(Myoutput).Cells(l, 9) = Statavg_06
Sheets(Myoutput).Cells(l, 10) = Statavg_07
Sheets(Myoutput).Cells(l, 11) = Statavg_08
Next l
End If
' ***
Next i
End Sub
This code gives me the same average l times for each year but I'd like to get an average for each value of the array which correponds to column c in sheet "output".
Any help you could give me would be valuable!
Also, as u see the coding is not very nice, maybe there is a way of looping to get the average for every column instead of doing it manually, I tried using application.index(rng.Columns(n)) but doesn't work, i think I'm not declaring it properly.
Thanks,
j
I'm struggling to set a macro to calculate the average value over countries for different variables (i = 1 To 8). The % by country, by variable and by year is on sheet "output" and the average % by variable and by year should be in sheet "Average". Here is a bit of the code I need to work around.
Sub Average_AF()
'
Dim tabcode(1 To 8) As String
Dim Statavg_99 As Double
Dim Statavg_00 As Double
Dim Statavg_01 As Double
Dim Statavg_02 As Double
Dim Statavg_03 As Double
Dim Statavg_04 As Double
Dim Statavg_05 As Double
Dim Statavg_06 As Double
Dim Statavg_07 As Double
Dim Statavg_08 As Double
Dim l As Variant
Dim rng As Range
tabcode(1) = "00_"
tabcode(2) = "01_"
tabcode(3) = "02_"
tabcode(4) = "03_"
tabcode(5) = "04_"
tabcode(6) = "05_"
tabcode(7) = "06_"
tabcode(8) = "02B"
Sheets("Output").Select
Sheets.Add
' RENAME the new sheet
ActiveSheet.Name = "Average"
Myoutput = ActiveSheet.Name
Sheets("Output").Select
Set rng = Range("c3:m220")
Statavg_99 = Application.Average(rng.Columns(2))
Statavg_00 = Application.Average(rng.Columns(2))
Statavg_01 = Application.Average(rng.Columns(3))
Statavg_02 = Application.Average(rng.Columns(4))
Statavg_03 = Application.Average(rng.Columns(5))
Statavg_04 = Application.Average(rng.Columns(6))
Statavg_05 = Application.Average(rng.Columns(7))
Statavg_06 = Application.Average(rng.Columns(8))
Statavg_07 = Application.Average(rng.Columns(9))
Statavg_08 = Application.Average(rng.Columns(10))
For l = 3 To 9
Sheets(Myoutput).Cells(l, 2) = Statavg_99
Sheets(Myoutput).Cells(l, 3) = Statavg_00
Sheets(Myoutput).Cells(l, 4) = Statavg_01
Sheets(Myoutput).Cells(l, 5) = Statavg_02
Sheets(Myoutput).Cells(l, 6) = Statavg_03
Sheets(Myoutput).Cells(l, 7) = Statavg_04
Sheets(Myoutput).Cells(l, 8) = Statavg_05
Sheets(Myoutput).Cells(l, 9) = Statavg_06
Sheets(Myoutput).Cells(l, 10) = Statavg_07
Sheets(Myoutput).Cells(l, 11) = Statavg_08
Next l
End If
' ***
Next i
End Sub
This code gives me the same average l times for each year but I'd like to get an average for each value of the array which correponds to column c in sheet "output".
Any help you could give me would be valuable!
Also, as u see the coding is not very nice, maybe there is a way of looping to get the average for every column instead of doing it manually, I tried using application.index(rng.Columns(n)) but doesn't work, i think I'm not declaring it properly.
Thanks,
j