NARAYANK991
Excel Ninja
Hi ,
Sorry , but the uploaded file is a .xlsx file , which cannot have any code in it.
Narayan
Sorry , but the uploaded file is a .xlsx file , which cannot have any code in it.
Narayan
subscript out of range, even though the macro is being run from within the active workbook/sheet
- Thread starter monoj chakraborty
- Start date
monoj chakraborty
Member
narayan - sorry for that. would it work for you if I pasted the code here, as I had done before or is there another way to send the code?
monoj chakraborty
Member
Dear narayan,
I am pasting the code below :
I am pasting the code below :
Code:
Sub quartersummary()
'
' quartersummary Macro
' variables
' name of the worksheet, period TY LY, quarters, datarange for ty and ly,shift rows for category, shift cols for country, shift row
'and col for changing quarters
Dim catrow As Integer ' row variable for category
Dim startcol As Integer ' fix start column for this year and last year
Dim shiftcol As Integer 'shift column to change for country
Dim colrange As Integer ' define column for selecting data range
Dim passforQ As Integer 'quarter runs
Dim passforY As Integer 'runs for period
Dim P1, P2 As String 'to set the column to select start and end match periods while offsetting
Dim formulastring As String ' this is the string to define the formula and workaround the character limit
Dim spanstart, spanend As Integer 'to set the span for offsetting
category = Array("insect", "air care", "home cleaning", "auto care", "shoe care") 'array to select category
country = Array("cambodia", "laos", "myanmar", "brunei") 'array to select worksheet for country
fy17 = Array("01jul_fy17", "03sep_fy17", "04oct_fy17", "06dec_fy17", "07jan_fy17", "09mar_fy17", "10apr_fy17", "12jun_fy17") 'array to define the periods to choose from while _
offsetting ty
fy16 = Array("01jul", "03sep", "04oct", "06dec", "07jan", "09mar", "10apr", "12jun") 'array to define the periods to choose from while _
offsetting ly
For passforQ = 3 To 30 Step 9
For passforY = 1 To 2
Select Case passforY
Case Is = 1
startcol = 2
colrange = 47
Case Is = 2
startcol = 7
colrange = 34
End Select
Select Case passforQ And passforY
Case Is = 3 And 1
P1 = fy17(0)
P2 = fy17(1)
Case Is = 12 And 1
P1 = fy17(2)
P2 = fy17(3)
Case Is = 21 And 1
P1 = fy17(4)
P2 = fy17(5)
Case Is = 30 And 1
P1 = fy17(6)
P2 = fy17(7)
Case Is = 3 And 2
P1 = fy16(0)
P2 = fy16(1)
Case Is = 12 And 2
P1 = fy16(2)
P2 = fy16(3)
Case Is = 21 And 2
P1 = fy16(4)
P2 = fy16(5)
Case Is = 30 And 2
P1 = fy16(6)
P2 = fy16(7)
End Select
For shiftcol = startcol To startcol + 3
Select Case shiftcol
Case Is = startcol
country = country(0)
spanstart = 4
spanend = 119
Case Is = startcol + 1
country = country(1)
spanstart = 4
spanend = 136
Case Is = startcol + 2
country = country(2)
spanstart = 3
spanend = 156
Case Is = startcol + 3
country = country(3)
spanstart = 4
spanend = 262
End Select
For catrow = 3 To 7
formulastring = "=sum(offset(" & "'" & country & "'!" & "r" & spanstart & "c" & colrange & ":r" & spanend & "c" _
& colrange + 11 & ",0,(match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & _
colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)-1)" & "," & (spanend - spanstart + 1) & _
",(match(" & """" & P2 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & colrange & ":r" _
& spanstart - 1 & "c" & colrange + 11 & ",0)-match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & _
spanstart - 1 & "c" & colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)+1))*(" & "'" & country & "'!" _
& "r" & spanstart & "c3:r" & spanend & "c3=""" & category(catrow - 3) & """))/1000/36.11"
With Sheets("category summary by quarter").Cells(catrow, startcol)
.FormulaArray = formulastring
End With
Next catrow
Next shiftcol
Next passforY
Next passforQ
End SubNARAYANK991
Excel Ninja
Hi ,
Thanks. Give me a day to go through it.
Narayan
Thanks. Give me a day to go through it.
Narayan
monoj chakraborty
Member
hey narayan - i have modified the code slightly below to correct some of the looping errors in the earlier code I shared, as it was writing over and over again the same country. This is now taken care of.
what is the continuing nagging problem though is that the "catrow" variable should change with the change in quarter value ("passforq"). with change in passforq, it should pick up new values for "p1" and "p2" from the arrays. This is happening for the 1st pass and when the passforq is supposed to go to the next loop, its value is changing, P1, p2 values are also changing, country is changing as it should, but when it comes to executing the formulastring, its throwing error 9 ("subscript out of range"); at the formula string, it should change the "country" variable to its latest value, but it is continuing to be at the last value from the last loop hence the range for data not matching the country name (in this case the formula should pick up the data range from cambodia, instead, its still in brunei but brunei does not have that data range)
secondly, for the variable fx - its rounding off to 36/33 instead of 36.11 and 33.4, thus giving small differences in value from the worksheets. Is there a way to specify the decimal places while naming the variable or inside the formula?
what is the continuing nagging problem though is that the "catrow" variable should change with the change in quarter value ("passforq"). with change in passforq, it should pick up new values for "p1" and "p2" from the arrays. This is happening for the 1st pass and when the passforq is supposed to go to the next loop, its value is changing, P1, p2 values are also changing, country is changing as it should, but when it comes to executing the formulastring, its throwing error 9 ("subscript out of range"); at the formula string, it should change the "country" variable to its latest value, but it is continuing to be at the last value from the last loop hence the range for data not matching the country name (in this case the formula should pick up the data range from cambodia, instead, its still in brunei but brunei does not have that data range)
secondly, for the variable fx - its rounding off to 36/33 instead of 36.11 and 33.4, thus giving small differences in value from the worksheets. Is there a way to specify the decimal places while naming the variable or inside the formula?
Code:
Sub quartersummary()
'
' quartersummary Macro
' variables
' name of the worksheet, period TY LY, quarters, datarange for ty and ly,shift rows for category, shift cols for country, shift row
'and col for changing quarters
Dim catrow As Integer ' row variable for category
Dim startcol As Integer ' fix start column for this year and last year
Dim shiftcol As Integer 'shift column to change for country
Dim colrange As Integer ' define column for selecting data range
Dim passforQ As Integer 'quarter runs
Dim passforY As Integer 'runs for period
Dim P1, P2, country As String 'to set the column to select start and end match periods while offsetting
Dim formulastring As String ' this is the string to define the formula and workaround the character limit
Dim spanstart, spanend As Integer 'to set the span for offsetting
Dim fx As Integer 'variable to set the fx rate for fy17 and fy16
category = Array("insect", "air care", "cleaner", "auto care", "shoe care") 'array to select category
selcountry = Array("cambodia", "laos", "myanmar", "brunei") 'array to select worksheet for country
fy17 = Array("01jul_fy17", "03sep_fy17", "04oct_fy17", "06dec_fy17", "07jan_fy17", "09mar_fy17", "10apr_fy17", "12jun_fy17") 'array to define the periods to choose from while _
offsetting ty
fy16 = Array("01jul", "03sep", "04oct", "06dec", "07jan", "09mar", "10apr", "12jun") 'array to define the periods to choose from while _
offsetting ly
For passforQ = 3 To 30 Step 9
For passforY = 1 To 2
Select Case passforY
Case Is = 1
startcol = 2
colrange = 47
fx = 36.11
Case Is = 2
startcol = 7
colrange = 34
fx = 33.4
End Select
Select Case passforQ And passforY
Case Is = 3, 1
P1 = fy17(0)
P2 = fy17(1)
Case Is = 12, 1
P1 = fy17(2)
P2 = fy17(3)
Case Is = 21, 1
P1 = fy17(4)
P2 = fy17(5)
Case Is = 30, 1
P1 = fy17(6)
P2 = fy17(7)
Case Is = 3, 2
P1 = fy16(0)
P2 = fy16(1)
Case Is = 12, 2
P1 = fy16(2)
P2 = fy16(3)
Case Is = 21, 2
P1 = fy16(4)
P2 = fy16(5)
Case Is = 30, 2
P1 = fy16(6)
P2 = fy16(7)
End Select
For shiftcol = startcol To startcol + 3
Select Case shiftcol
Case Is = 2, 7
country = selcountry(0)
spanstart = 4
spanend = 119
Case Is = 3, 8
country = selcountry(1)
spanstart = 4
spanend = 136
Case Is = 4, 9
country = selcountry(2)
spanstart = 3
spanend = 156
Case Is = 5, 10
country = selcountry(3)
spanstart = 4
spanend = 262
End Select
For catrow = passforQ To passforQ + 4
If country <> "brunei" Then
formulastring = "=sum(offset(" & "'" & country & "'!" & "r" & spanstart & "c" & colrange & ":r" & spanend & "c" _
& colrange + 11 & ",0,(match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & _
colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)-1)" & "," & (spanend - spanstart + 1) & _
",(match(" & """" & P2 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & colrange & ":r" _
& spanstart - 1 & "c" & colrange + 11 & ",0)-match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & _
spanstart - 1 & "c" & colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)+1))*(" & "'" & country & "'!" _
& "r" & spanstart & "c3:r" & spanend & "c3=""" & category(catrow - 3) & """))/1000/" & fx & ""
Else
formulastring = "=sum(offset(" & "'" & country & "'!" & "r" & spanstart & "c" & colrange & ":r" & spanend & "c" _
& colrange + 11 & ",0,(match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & _
colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)-1)" & "," & (spanend - spanstart + 1) & _
",(match(" & """" & P2 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & colrange & ":r" _
& spanstart - 1 & "c" & colrange + 11 & ",0)-match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & _
spanstart - 1 & "c" & colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)+1))*(" & "'" & country & "'!" _
& "r" & spanstart & "c3:r" & spanend & "c3=""" & category(catrow - 3) & """))/1000"
End If
With Sheets("category summary by quarter").Cells(catrow, shiftcol)
.FormulaArray = formulastring
End With
Next catrow
Next shiftcol
Next passforY
Next passforQ
End SubNARAYANK991
Excel Ninja
Hi ,
The problem was that catrow was taking values beyond the bounds of the Category array !
Change the following lines of code instead of the existing code :
Narayan
The problem was that catrow was taking values beyond the bounds of the Category array !
Change the following lines of code instead of the existing code :
Code:
Sub quartersummary()
'
' quartersummary Macro
' variables
' name of the worksheet, period TY LY, quarters, datarange for ty and ly,shift rows for category, shift cols for country, shift row
'and col for changing quarters
Dim catrow As Integer ' row variable for category
Dim startcol As Integer ' fix start column for this year and last year
Dim shiftcol As Integer 'shift column to change for country
Dim colrange As Integer ' define column for selecting data range
Dim passforQ As Integer 'quarter runs
Dim passforY As Integer 'runs for period
Dim P1, P2, country As String 'to set the column to select start and end match periods while offsetting
Dim formulastring As String ' this is the string to define the formula and workaround the character limit
Dim spanstart, spanend As Integer 'to set the span for offsetting
Dim fx As Integer 'variable to set the fx rate for fy17 and fy16
Category = Array("insect", "air care", "cleaner", "auto care", "shoe care") 'array to select category
selcountry = Array("cambodia", "laos", "myanmar", "brunei") 'array to select worksheet for country
fy17 = Array("01jul_fy17", "03sep_fy17", "04oct_fy17", "06dec_fy17", "07jan_fy17", "09mar_fy17", "10apr_fy17", "12jun_fy17") 'array to define the periods to choose from while _
offsetting ty
fy16 = Array("01jul", "03sep", "04oct", "06dec", "07jan", "09mar", "10apr", "12jun") 'array to define the periods to choose from while _
offsetting ly
For passforQ = 3 To 30 Step 9
For passforY = 1 To 2
Select Case passforY
Case Is = 1
startcol = 2
colrange = 47
fx = 36.11
Case Is = 2
startcol = 7
colrange = 34
fx = 33.4
End Select
Select Case passforQ + passforY
Case Is = 4
P1 = fy17(0)
P2 = fy17(1)
Case Is = 13
P1 = fy17(2)
P2 = fy17(3)
Case Is = 22
P1 = fy17(4)
P2 = fy17(5)
Case Is = 31
P1 = fy17(6)
P2 = fy17(7)
Case Is = 5
P1 = fy16(0)
P2 = fy16(1)
Case Is = 14
P1 = fy16(2)
P2 = fy16(3)
Case Is = 23
P1 = fy16(4)
P2 = fy16(5)
Case Is = 32
P1 = fy16(6)
P2 = fy16(7)
End Select
For shiftcol = startcol To startcol + 3
Select Case shiftcol
Case Is = 2, 7
country = selcountry(0)
spanstart = 4
spanend = 119
Case Is = 3, 8
country = selcountry(1)
spanstart = 4
spanend = 136
Case Is = 4, 9
country = selcountry(2)
spanstart = 3
spanend = 156
Case Is = 5, 10
country = selcountry(3)
spanstart = 4
spanend = 262
End Select
For catrow = passforQ To passforQ + 4
rowval = catrow Mod 9
If country <> "brunei" Then
formulastring = "=sum(offset(" & "'" & country & "'!" & "r" & spanstart & "c" & colrange & ":r" & spanend & "c" _
& colrange + 11 & ",0,(match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & _
colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)-1)" & "," & (spanend - spanstart + 1) & _
",(match(" & """" & P2 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & colrange & ":r" _
& spanstart - 1 & "c" & colrange + 11 & ",0)-match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & _
spanstart - 1 & "c" & colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)+1))*(" & "'" & country & "'!" _
& "r" & spanstart & "c3:r" & spanend & "c3=""" & Category(rowval - 3) & """))/1000/" & fx & ""
Else
formulastring = "=sum(offset(" & "'" & country & "'!" & "r" & spanstart & "c" & colrange & ":r" & spanend & "c" _
& colrange + 11 & ",0,(match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & _
colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)-1)" & "," & (spanend - spanstart + 1) & _
",(match(" & """" & P2 & """" & "," & "'" & country & "'!" & "r" & spanstart - 1 & "c" & colrange & ":r" _
& spanstart - 1 & "c" & colrange + 11 & ",0)-match(" & """" & P1 & """" & "," & "'" & country & "'!" & "r" & _
spanstart - 1 & "c" & colrange & ":r" & spanstart - 1 & "c" & colrange + 11 & ",0)+1))*(" & "'" & country & "'!" _
& "r" & spanstart & "c3:r" & spanend & "c3=""" & Category(rowval - 3) & """))/1000"
End If
With Sheets("category summary by quarter").Cells(catrow, shiftcol)
.FormulaArray = formulastring
End With
Next catrow
Next shiftcol
Next passforY
Next passforQ
End Submonoj chakraborty
Member
narayan - apologies for the late reply. It works fine now. thanks a bunch.
I now have another problem - when I am trying to run the same macro in a different workbook (and only that workbook is open and active while running the macro), it gives error 9, at the part where I am referencing the worksheet.
I know this must be happening because its not able to find the referenced worksheet in the workbook, but not able to figure out why! can u help?
[
Sub categorysummary()
'
' categorysummary Macro
' variables
' name of the worksheet, period TY , months, datarange for ty ,shift rows for category, shift cols for country, shift row
'and col for changing months
Dim passforC As Integer 'looping for countries
Dim catrow As Integer ' row variable for category
Dim shiftcol As Integer 'shift column to change for country
Dim spanstart, spanend As Integer 'to set the span for offsetting
Dim p1, country, category As String 'P1 matches the month in the array; country and category are string variables _
which will take their values from the respective arrays
Dim fx As Integer
selcategory = Array("insect", "air care", "cleaner", "automotive care", "shoe care") 'array to select category
selcountry = Array("cambodia THB", "laos THB", "myanmar THB", "brunei USD") 'array to select worksheet for country
fy17 = Array("01jul_fy17", "02aug_fy17", "03sep_fy17", "04oct_fy17", "05nov_fy17", "06dec_fy17", _
"07jan_fy17", "08feb_fy17", "09mar_fy17", "10apr_fy17", "11may_fy17", "12jun_fy17")
ActiveWorkbook.Activate
For passforC = 2 To 26 Step 8
Select Case passforC
Case Is = 2
country = selcountry(0)
spanstart = 4
spanend = 125
fx = 36.11
Case Is = 10
country = selcountry(1)
spanstart = 4
spanend = 146
fx = 36.11
Case Is = 18
country = selcountry(2)
spanstart = 4
spanend = 157
fx = 36.11
Case Is = 26
country = selcountry(3)
spanstart = 4
spanend = 250
fx = 1
End Select
For shiftcol = 2 To 13
p1 = fy17(shiftcol - 2)
For catrow = passforC To passforC + 4
Select Case catrow
Case 2, 10, 18, 26
category = selcategory(0)
Case 3, 11, 19, 27
category = selcategory(1)
Case 4, 12, 20, 28
category = selcategory(2)
Case 5, 13, 21, 29
category = selcategory(3)
Case 6, 14, 22, 30
category = selcategory(4)
End Select
Sheets("fy summary category").Cells(catrow, shiftcol).Select
ActiveCell.FormulaArray = "=+SUM(('" & country & "'!R" & spanstart & "C47:R" & spanend & _
"C58)*('" & country & "'!R" & spanstart & "C3:R" & spanend & "C3=""" & category & _
""")*('" & country & "'!R3C47:R3C58=" & """" & p1 & """" & ")/1000/" & fx & ")"
Next catrow
Next shiftcol
Next passforC
End Sub
]
I now have another problem - when I am trying to run the same macro in a different workbook (and only that workbook is open and active while running the macro), it gives error 9, at the part where I am referencing the worksheet.
I know this must be happening because its not able to find the referenced worksheet in the workbook, but not able to figure out why! can u help?
[
Sub categorysummary()
'
' categorysummary Macro
' variables
' name of the worksheet, period TY , months, datarange for ty ,shift rows for category, shift cols for country, shift row
'and col for changing months
Dim passforC As Integer 'looping for countries
Dim catrow As Integer ' row variable for category
Dim shiftcol As Integer 'shift column to change for country
Dim spanstart, spanend As Integer 'to set the span for offsetting
Dim p1, country, category As String 'P1 matches the month in the array; country and category are string variables _
which will take their values from the respective arrays
Dim fx As Integer
selcategory = Array("insect", "air care", "cleaner", "automotive care", "shoe care") 'array to select category
selcountry = Array("cambodia THB", "laos THB", "myanmar THB", "brunei USD") 'array to select worksheet for country
fy17 = Array("01jul_fy17", "02aug_fy17", "03sep_fy17", "04oct_fy17", "05nov_fy17", "06dec_fy17", _
"07jan_fy17", "08feb_fy17", "09mar_fy17", "10apr_fy17", "11may_fy17", "12jun_fy17")
ActiveWorkbook.Activate
For passforC = 2 To 26 Step 8
Select Case passforC
Case Is = 2
country = selcountry(0)
spanstart = 4
spanend = 125
fx = 36.11
Case Is = 10
country = selcountry(1)
spanstart = 4
spanend = 146
fx = 36.11
Case Is = 18
country = selcountry(2)
spanstart = 4
spanend = 157
fx = 36.11
Case Is = 26
country = selcountry(3)
spanstart = 4
spanend = 250
fx = 1
End Select
For shiftcol = 2 To 13
p1 = fy17(shiftcol - 2)
For catrow = passforC To passforC + 4
Select Case catrow
Case 2, 10, 18, 26
category = selcategory(0)
Case 3, 11, 19, 27
category = selcategory(1)
Case 4, 12, 20, 28
category = selcategory(2)
Case 5, 13, 21, 29
category = selcategory(3)
Case 6, 14, 22, 30
category = selcategory(4)
End Select
Sheets("fy summary category").Cells(catrow, shiftcol).Select
ActiveCell.FormulaArray = "=+SUM(('" & country & "'!R" & spanstart & "C47:R" & spanend & _
"C58)*('" & country & "'!R" & spanstart & "C3:R" & spanend & "C3=""" & category & _
""")*('" & country & "'!R3C47:R3C58=" & """" & p1 & """" & ")/1000/" & fx & ")"
Next catrow
Next shiftcol
Next passforC
End Sub
]