subscript out of range, even though the macro is being run from within the active workbook/sheet

monoj chakraborty · Jul 9, 2016

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

Case Is = 2

startcol = 7

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("sheet19").Range(catrow, startcol).Address ' this is the part where it shows the run time error..."subscript of out range"

.FormulaArray = formulastring

End With

Next catrow

Next shiftcol

Next passforY

Next passforQ

End Sub

NARAYANK991 · Jul 9, 2016

Hi ,

In the following segment :

With Sheets("sheet19").Range(catrow, startcol).Address
.FormulaArray = formulastring
EndWith

the first line of code is giving you an error.

The subscript error is usually generated when the referred to object does not exist ; in this case , if there is no sheet such as Sheet19 , Excel will generate an error.

The second possibility is the Range construct ; when you use the Range keyword , the parameter to the Range property should be a range reference such as A1 , B7:C13 in the form of a string e.g.

Sheets("Sheet19").Range("J6:M17").Address

should be valid.

If you want to use the row number and column number , then the property to be used is the Cells property , as in :

Sheets("Sheet19").Cells(catrow , startcol).Address

----------------------------------------------------------------------------------

However , despite all of the above , the FormulaArray property , which is being used in the next line of code , pertains to a Range object ; thus your earlier line cannot use the Address property.

Your rewritten code would therefore be :

With Sheets("sheet19").Cells(catrow, startcol)
.FormulaArray = formulastring
EndWith

Narayan

monoj chakraborty · Jul 9, 2016

hey Narayan - that worked!!! thank you so much. And now I have run into my old bug again, which is run time error 1004, probably b'cos of the length of the formula string. Have tried to break it into 2 or 3 dummy parts. Does not seem to work. can u help with breaking up the formula string into manageable dummies.

NARAYANK991 · Jul 9, 2016

Hi ,

Please post the formula you want entered in the worksheet cell , and also the code through which you want this formula to be entered.

There are quite a few threads in this forum addressing this topic :

http://chandoo.org/forum/threads/vba-long-array-formula.15957/

http://chandoo.org/forum/threads/formulaarray-replace-assistance.29303/

Narayan

monoj chakraborty · Jul 9, 2016

[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)"]

NARAYANK991 · Jul 9, 2016

Hi ,

This is not enough. I have already posted what is required :

1. The formula as you would enter it in a worksheet cell , specifying the cell in which you wish to enter it.

2. The complete code with variable declarations , which you are using to enter it.

Narayan

monoj chakraborty · Jul 9, 2016

[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 AsInteger' row variable for categoryDim startcol AsInteger' fix start column for this year and last yearDim shiftcol AsInteger'shift column to change for countryDim colrange AsInteger' define column for selecting data rangeDim passforQ AsInteger'quarter runsDim passforY AsInteger'runs for periodDim P1, P2 AsString'to set the column to select start and end match periods while offsettingDim formulastring AsString' this is the string to define the formula and workaround the character limitDim spanstart, spanend AsInteger'to set the span for offsetting
category = Array("insect", "air care", "home cleaning", "auto care", "shoe care") 'array to select categorycountry = Array("'cambodia'!", "'laos'!", "'myanmar'!", "'brunei'!") 'array to select worksheet for countryfy17 = 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 tyfy16 = 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

SelectCase passforY

CaseIs = 1

startcol = 2

CaseIs = 2

startcol = 7

EndSelect

SelectCase passforQ And passforY

CaseIs = 3 And 1

P1 = fy17(0)
P2 = fy17(1)

CaseIs = 12 And 1

P1 = fy17(2)
P2 = fy17(3)

CaseIs = 21 And 1

P1 = fy17(4)
P2 = fy17(5)

CaseIs = 30 And 1

P1 = fy17(6)
P2 = fy17(7)

CaseIs = 3 And 2

P1 = fy16(0)
P2 = fy16(1)

CaseIs = 12 And 2

P1 = fy16(2)
P2 = fy16(3)

CaseIs = 21 And 2

P1 = fy16(4)
P2 = fy16(5)

CaseIs = 30 And 2

P1 = fy16(6)
P2 = fy16(7)

EndSelect

For shiftcol = startcol To startcol + 3

SelectCase shiftcol

CaseIs = startcol

country = country(0)
spanstart = 4
spanend = 119

CaseIs = startcol + 1

country = country(1)
spanstart = 4
spanend = 136

CaseIs = startcol + 2

country = country(2)
spanstart = 3
spanend = 156

CaseIs = startcol + 3

country = country(3)
spanstart = 4
spanend = 262

EndSelect

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("sheet19").cells(catrow, startcol)
.FormulaArray = formulastring

EndWith

Next catrow

Next shiftcol

Next passforY

Next passforQ

EndSub]

NARAYANK991 · Jul 9, 2016

Hi ,

What would the formula look like if you were to enter it in a worksheet cell ?

Without this , it will take me some more time to figure out what value each variable will have and then decode the formula.

Narayan

monoj chakraborty · Jul 10, 2016

hey narayan, i am so sorry I am confusing you. here you go - this is how the excel formula is going to look like if entered in a cell :
"
=+SUM(OFFSET(Cambodia!$AU$4:$BF$132,0,0,129,(MATCH("03sep_fy17",Cambodia!$AU$3:$BF$3,0)-MATCH("01jul_fy17",Cambodia!$AU$3:$BF$3,0)+1))*(Cambodia!$C$4:$C$132="insect")/1000/36.11)
"

NARAYANK991 · Jul 10, 2016

Hi ,

You have still not mentioned the cell in which this formula is to be entered.

Since the formula is in A1 style notation , while the VBA code is in R1C1 style of notation , unless I know the cell in which the formula will be entered , it is difficult to understand whether the two are identical.

Narayan

NARAYANK991 · Jul 10, 2016

Hi ,

The following formula code works , since the length of the formula still does not exceed 255 characters.

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"

Only before the formula assignment is done , you need to assign a valid value to the VBA variable colrange.

I have removed the single quote and excalamation marks from the definition of the VBA variable country , which is now :

country = Array("cambodia", "laos", "myanmar", "brunei") 'array to select worksheet for country

If you put these back , then you will have to remove them from the assignment to the VBA variable formulastring.

Narayan

monoj chakraborty · Jul 10, 2016

hey narayan - i copied your formulastring exactly into my code and replaced my formulastring with yours and also set colrange to a valid start value. I still seem to be getting the same 1004, error.
could you help please.

NARAYANK991 · Jul 10, 2016

Hi ,

Please upload your file.

Narayan

NARAYANK991 · Jul 10, 2016

Hi ,

See the uploaded file.

Narayan

monoj chakraborty · Jul 10, 2016

seems to ok to me at least for one full loop of the rowchange - but cant figure out why the code upon F8 debugging is coming to a halt at this line ....have copied your formula exactly.

monoj chakraborty · Jul 10, 2016

when i counted the characters with spaces, its showing 744 - could that be the reason for this error?
sometime back you had helped me out with another array formula, by splitting the string into two thru a dummy string as below :
[
datarange = "=sum((" & wbPath & wbName & wksheet & "!r8c" & colforperiod & ":r46c" & colforperiod + shiftcol1 & ")" & "+dummy)"

catselect = "*(" & wbPath & wbName & wksheet & "!$B$8:$B$46=""" & Category(rowforcat - 1) & """))"

With Sheets("bta").Range("b3")

.FormulaArray = datarange
.Replace "+dummy)", catselect, xlPart
End With]
could that be the solution - i tried this route too; somehow getting the same error.
should we use three dummy strings then?
thanks for your patience...

NARAYANK991 · Jul 10, 2016

Hi ,

Now , all of a sudden , you have introduced the workbook path and workbook name.

Unless you upload your complete workbook with the data and the code in it , I am sorry , but I cannot help any further.

Narayan

monoj chakraborty · Jul 11, 2016

have added one worksheet and the summary sheet - the macro is supposed to pull the relevant data from the sheets named after countries (like cambodia) and insert in the summary sheet called "category summary by quarter"
will this work for you now?

NARAYANK991 · Jul 11, 2016

Hi ,

See the file now. You will need to do a few things :

1. Introduce the worksheet tabs for the other countries.

2. Ensure that the formula is written to the correct cells for the other countries ; at present , the code overwrites the data written for Cambodia with data for the other countries.

Narayan

monoj chakraborty · Jul 12, 2016

hey narayan - what is the final code you are running for this sheet? the variables are so defined that it will change the sheets automatically as per the array defined. My problem is that i am continuing to get the same "range class error" while running the code. I have now even tried breaking the formulastring into two strings as below :
[
formulastring1 = "=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) & _
"+dummy)"

formulatring2 = ",(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 = formulastring1
.Replace "+dummy)", formulastring2, xlPart

but the programme halts at the line as below :

while debugging all variables are returning the values that should be assigned to them at every change of loop, but still am not able to figure out why am i getting the range class error continually.

NARAYANK991 · Jul 15, 2016

Hi ,

Please upload your complete workbook , with all the country worksheet tabs , the data and the code.

Narayan

monoj chakraborty · Jul 16, 2016

hey narayan,

the file even after zipping is coming to around 1.5mb and I am getting an error. Is there a work around?

NARAYANK991 · Jul 16, 2016

Hi ,

You can upload your workbook to a public file-sharing site such as DropBox , give people access to download and open the file , and then post the shared access link here in this same thread.

Narayan

monoj chakraborty · Jul 17, 2016

hey narayan - see if this works. Thanks for the advice. Here is the dropbox link:

https://www.dropbox.com/s/7b7ksfhrz8r0ppy/CLMB Jul step 2.xlsx?dl=0

the code can be found in the under the list of macros - its called the "quartersummary"

let me know if it works for you

monoj chakraborty · Aug 12, 2016

hey narayan - it seems we lost each other here after a very interesting exchange. I sent you the file and the code etc thru' the dropbox link. Hope you got a chance to look at it.
It would be really great if you could take a shot at it - will really help me automate some very important MIS as we are largely an excel-driven organisation
thanking in advance

subscript out of range, even though the macro is being run from within the active workbook/sheet

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Member

Excel Ninja

Excel Ninja

Member

Excel Ninja

Excel Ninja

Attachments

Member

Member

Excel Ninja

Member

Attachments

Excel Ninja

Attachments

Member

Excel Ninja

Member

Excel Ninja

Member

Member