Setting Worksheets - Subscript Out Of Range

Feb 29, 2012

Private Sub Worksheet_Activate()

On Error Resume Next
Set sT = Sheets("Report")
On Error GoTo 0
On Error GoTo nendR
If Not sT Is Nothing Then

[Code] ........

I get a Subscript out of range at Set cT = Sheets("RGA by Customer") line.

It baffles me that when neither sheet exists and the first part runs there is no problem, but when it gets the the above mentioned line I get this error. Same exact code, just a different sheet name.

Again, when the sheet exists there is no problem. Only when I click on Sheet1 (or start up the file) and this sheet doesn't exist do I get the error.

View 4 Replies


Subscript Out Of Range - Error Relating To Setting Workbook

Jul 11, 2014

I'm trying to do the following

Dim wkb As Workbook
Dim wkb2 As Workbook

Set wkb = ActiveWorkbook
Set wkb2 = Workbooks("F:SuppliersBT Monthly Invoice2014BT Macros.xlsm")

but get a subscript out of range error relating to setting wkb2

The file BT Macros is open - this is where the Macro is stored. What have I done wrong when trying to reference it?

View 5 Replies View Related

Subscript Out Of Range :: Both Worksheets

Apr 27, 2009

I have this code below that keeps giving me a subscript out of range error and I don't know why I got it to run once but can't figure out the problem now...

View 4 Replies View Related

Subscript Out Of Range When Selecting Range In Closed Worksheet

Apr 28, 2006

I am receiving a 'subscript out of range' error on the lines of code below.

I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.

Below is a snippet from the immediate window which indicates what the values are:
completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls
cashsheetname Formatted Sheet
cashcurrcolumn A
cashfirstrow 2
cashlastrow 876

Also the workbooks are both closed at this point (but it makes no difference)

Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _
Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value
Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _
Range((PELCurrColumn & PELlastrow)).Value

View 8 Replies View Related

Setting Same Print Area And Settings Across All Worksheets?

Feb 2, 2014

Is it possible to set the same print area and print settings i.e. Landscape and 1 page tall by 2 pages wide, across all worksheets in an excel file? I have a file with 365 worksheets. They all have the exact same layout and need to be printed off daily. I can set the print settings every day manually but was wondering if there is an easy way to do them all in one go?

View 4 Replies View Related

Subscript Out Of Range ....

Aug 6, 2009

I had the worksheet (VCollinsville) misspelled in the Workbook.

I have the following code (it's long but my problem is at the end where I stop): ...

View 4 Replies View Related

Subscript Out Of Range ..

Nov 7, 2009

I have a master spreadsheet in XL2003 (SP3) running Vista. The master sheet, called "DCam IV-11.xls" is a large calculation sheet that is driven by user entered boundary conditions. The boundary conditions are stored in different input files named "******-inp.xls". I use this method to store BCs in small spreadsheet files rather than saving full master spreadsheets in files.

View 6 Replies View Related

Subscript Out Of Range

Jul 26, 2006

keep getting an error message saying subscript out of range.

What am i missing?

'setting up the variable in the projectManagersWorkbook object
Set projectManagersWorkbook.Wkbk = Workbooks(getfileName(projectManagersWorkbook.filePath))
Set projectManagersWorkbook = projectManagersWorkbook.Wkbk.Worksheets("project managers")
Set projectManagersWorkbookLookupCell = projectManagersWorkbook.wks.Range("A2")
' searching for the Last Status Column from the last PO spreadsheet
vlookupcolumn = 1
Do Until tempValue = "Project Manager"
tempValue = projectManagersWorkbookLookupCell.Offset(-1, vlookupcolumn)
vlookupcolumn = vlookupcolumn + 1

View 5 Replies View Related

Subscript Out Of Range Error

Jun 26, 2014

I keep getting an error message when running the following code.

I am not sure why, but I keep getting a "Run-Time error '9': Subscript out of range" error message.

View 3 Replies View Related

Getting Subscript Out Of Range Error

Jul 23, 2014

What I'm trying to do is change the text color of specific keywords in a spreadsheet. For example, I'd like to highlight the text of 'how to' in the following sentence "I want to know how to change text color", so that it looks like this "I want to know how to change text color".

I came across the following, and it works for only two terms (example, "how to" , "how do I"), but I have a string of about 15 terms I'd like to use, but anything I change over two, I get the subscript out of range error.

[Code] .....

View 13 Replies View Related

Subscript Out Of Range Error 9?

Feb 25, 2014

what I'm trying to do is automatically format Excel's track changes to look like Microsoft Word's track changes feature i.e. put in a strike-through when people make changes on a shared spreadsheet.

I found the following script from here: [URL] .....

Dim xLen As Integer
Dim x, y, z, addr As String


Unfortunately, that produces the "Subscript out of range (Error 9)."

Clicking debug will highlight 'Worksheets("History").Activate', but I'm assuming that will just be one problem among a few others.

I've also tried replacing the 'x' in 'Worksheets(x).Activate' with the name of the worksheet (with extension xlsm), but no joy.

View 1 Replies View Related

Debug: Subscript Out Of Range?

May 29, 2009

trying to copy some records across from once sheet to another?

butt it keeps debugging?

any idea why.

i'd like to keep the user defined type because it's neat and is good practice for myself?

View 9 Replies View Related

Subscript Out Of Range Error ...

Jul 14, 2009

I am having trouble in a dowhile loop that i have created. Its purpose is to copy an unknown amount of data points into an array while it counts how many data points it collects and how many of them are above a reference pressure. I have defined data_A(),data_points,above_reference, and reference_pressure all as integers. The error occurs in the second line of code.

View 14 Replies View Related

Subscript Out Of Range After Completion

Dec 31, 2009

I'm getting a "Subscript out of Range" error on a routine that was running fine a few days ago. It's part of a larger macro, so I've pasted the full code for that macro below. The problem is in the routine labled "Replace Regional Labels". What that's supposed to do (and DOES do) is to replace labels such as 110 or 11000-CSD with 11000. The error message comes up AFTER the routine has completed it's work. The offending line is:

View 2 Replies View Related

Subscript Out Of Range Error

Sep 21, 2011

I have a workbook called "Project" and in it there is a macro that pulls data out of another workbook called "Operations Report."

The macro works fine on my laptop. When I run the macro, I make sure that both workbooks are open, (and they are also saved in the same directory - not sure that matters).

I sent the two workbooks to a colleague and when she opens them both and runs the macro, she gets a "run error 9 subscript out of range" error. She too has saved them to the same directory.

why she might be getting the error when I do not? Here is the line where hers trips up:

With Workbooks("Operations Report").Worksheets("Sheet2")

I assume that the macro is not finding the "Operations Report" workbook.

Are there some checks we could do or test lines of code I could send to her to diagnose the problem?

View 9 Replies View Related

Error 9 Subscript Out Of Range

Dec 23, 2013

Error 9 - Subscript Out Of Range....All sheets are in workbook and spelt correctly....could it be in the Array or Loop part of the code.

It also in the userform have a combobox for Day,Afternoon and Night but it transfers all across.Need the code to just transfer what is selected from the combobox (could be a listbox as well).If day selected then only Day for the selected date,,,same for afternoon and night unless All selected in which all are transferred (DAy,Afternnon & Night)

This part of he code gives error

arrOP_PS(PSCounter, 1) = CDate(Data(CurrentRow, 1)) 'date


Dim Data
Dim arrOP_PS(1 To 6, 1 To 5), PSCounter As Long
Dim arrOP_MSI(1 To 6, 1 To 5), MSICounter As Long
Dim arrOP_MSE(1 To 6, 1 To 5), MSECounter As Long

Private Sub CommandButton1_Click()

[Code] ........

View 6 Replies View Related

Subscript Out Of Range With New Computer

Jul 22, 2014

I created a code and I just changed computer. The code that was working perfectly fine on my previous laptop is no longer working now.

ActiveWorkbook.SaveAs Filename:= _
"C:UsersUserDocumentsBCB MANAGEMENTBankArchiveData_TenantsData_Tenants_" & Left(strDate, 8) _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Set wbData = Workbooks("Data_Tenants_" & Left(strDate, 8))

When it reaches Set wbdata it says subscript out of range. I'm not sure how it cannot recognise the spreadsheet I just created and that is open and working.

View 5 Replies View Related

Subscript Out Of Range - VBA Macro

Aug 1, 2014

I am getting he Subscript out of range error when trying to run this macro. I have the macro on a macro workbook and I am referencing another spreadsheet.

Sub Dater()
Static Count As Integer
Dim Wb As Workbook
Dim Ws As Worksheet
Dim FileStr As String
Set Wb = Workbooks("Pending Approval by Responsible User")
Set Ws = Worksheets("Pending Approval by Responsible")

View 4 Replies View Related

VBA Error, Subscript Out Of Range, 9

Nov 16, 2007

Sub Split_By_Store()

x = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox x

a = "F1:F" & x

Range(a).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1"), Unique:=True

y = Cells(Rows.Count, 10).End(xlUp).Row

For b = 2 To y
Worksheets.Add.Name = Worksheets("data").Cells(b, 10)
Next b

For c = 2 To x ' copy the data now
d = Worksheets("data").Cells(c, 6)
z = Worksheets(d).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("data").Range("A" & c & ":I" & c).Copy
Worksheets(d).Cells(z + 1, 1).PasteSpecial
Next c

End Sub

This code breaks on the line z =, I get a Subscript out of range:9 error. Basically this starts with a single sheet called "data". Column F has store number and column J is created as a helper column and each unique store number is transfered there... Then sheets are created for each unique store number from the helper column. Then it's supposed to transfer the area A:I from the "data" sheet for each store that is unique from column F and transfer that to the corresponding Store number for the sheet that was created for it in the earlier step.

View 9 Replies View Related

Subscript Out Of Range Even With Recorder

Feb 21, 2007

I have recorded a macro that will copy and paste information from text files to two separate sheets in a workbook. Previous versions of this have worked fine but now I have a "subscript out of range problem" for this line: Sheets("Raakadata").Select

I'm doing this project to someone else and the weird thing is I don't see this error in my computer but they have it in theirs. The workbook has been recently renamed to oljy_vedesta.xls but it should be correct in the code. The syntax should be fine as macro recorder has been used but still this error comes up with the another computer (all the required files are there).

Sub Tulosten_haku()
ChDir "C:Makro"
Workbooks.OpenText FileName:="C:MakroOLJY_VEDESTA_QC.TXT", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1))
Workbooks.OpenText FileName:="C:MakroOLJY_VEDESTA.TXT", Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1))..........................

View 2 Replies View Related

Subscript Out Of Range Error

Jul 25, 2007

I have in the attached workbook, a form which I run, the macro StartForm. This brings up the form, where in A, I select the sheet label Oval_An, and in B, I select the sheet Oval_DMA. The other drop down boxes are not in use yet. Once A and B are selected, the user hits the big subtract button, and normally gives the the new sheet, Final Results.

Yet, when I change the name of the forms, or allow the kTest compare variance box into the macro to be user definable via the form, the whole thing just stops working.

The error is found in the kTest macro, line 12 as a type mis match.

Redim q(1 To UBound(a, 1), 1 To 1)

I have had a whole lot of help from people with this, and I am new to VBA.

View 9 Replies View Related

Array Subscript Out Of Range

Aug 9, 2007

I have a form that finds an loads a record in textfields and combo boxes fine. When there are more than 1 record found, the user is presented with the FindAll option. Upon clicking find all, the list box gest populated with the records found. But instead, I am getting Run-Time error:'9' Subscript out of range in the following code block. It is occurring in the block starting with myArray: - MyArray(i, 0) = fndA:

Private Sub cmbFindAll_Click()
Dim MyArray(6, 12)
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI, fndJ, fndK, fndL As String
Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12 As String ' heading s for list
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("A6", Range("A65536").End(xlUp))
strFind = Me.txtWorkOrderNo.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
'load the headings

View 9 Replies View Related

Excel 2002 :: Setting Variable To Select All Worksheets In Workbook

Jan 20, 2012

I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).

I inserted this code into a rountine I do daily which works fine.

The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.

At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?

I've posted the code i got given below and have highlighted the bit where I have to name the sheets.

' Procedure : CreateConnection
' Author : KL (Kirill Lapin)
' Date : 18/08/2009


View 8 Replies View Related

Run Time Error - Subscript Out Of Range 9

Jul 16, 2014

I have three sheets in My workbook and one user form. based on selection of checkbox in userform, I want to activate the respective sheets. While clicking on add button, I am getting error message " Run Time Error : Subscript out of range 9". I am using following code

Option ExplicitPrivate Sub Add_Click()
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
TransferValues ctrl

[Code] .....

I am getting error on "Set ws = Sheets(Left(cb.Name, 1))" line.

View 9 Replies View Related

Runtime Error 9 - Subscript Out Of Range

Feb 12, 2014

I am trying to consolidate data in Sheet7 of all the files in a folder to a single file. In case a file doesn't contain Sheet7, i want to get the name of the workbook. The code is working fine, but after it loops it is returning Runtime Error 9 - Subscript out of range.

[Code] .....

View 5 Replies View Related

Error Message 'Subscript Out Of Range'

Aug 24, 2009

See if you can see what is wrong with this code - I have no idea. All the spreadsheet names are correct and I have used this syntax before, I don't know what I am doing wrong. The macro has a problem with the 3rd line

View 2 Replies View Related

Run Time Error 9 : Subscript Out Of Range

Aug 27, 2009

This program is supposed to take the value in two combo boxes and use them to populate pivot charts that are in other spreadsheets. So a user would select PS and AMI on sheet one (s) and it would change the pivot table on s1-s9 to look up those chosen fields. I keep getting a subscript out of range error, and I'm not sure if my pivottable.pivotlayout method is correct, but when I recorded a macro (in the very bottom) using activesheet, it worked. How can I fix this?

The error occurs at the first large text, the second large text is the recorded macro.

View 14 Replies View Related

Subscript Out Of Range :: Control Sheet

Sep 4, 2009

I have a workbook with 20 sheets, including a control sheet 'Validation Table'. By setting the required Background/Font in a cell named hdrDefault on sheet VT, I want to click a button on VT to invoke a macro to visit each sheet and set the named Header range, hdrxxx, to the same Background/Font. Originally had the code in a Private Sub linked to a button event, but this can't break out of the worksheet so have moved the Sub into a Module, and calling from the click event:


View 3 Replies View Related

(2000) Subscript Out Of Range Error 9?

Oct 30, 2009

I cant figure out why this bit of code wont run without an error. Code in RED is the problem.

I am running xl2000 on NT4.Old

Run-time error: '9':
Subscript out of range

View 3 Replies View Related

VBA Runtime Error 9 Subscript Out Of Range

Feb 21, 2012

The internet normally has the answers but I have not found one for this runtime error. I am just trying to paste some values from Excel into a word table using the following code

'Paste the values into the word table
Set MyTable = Union(Range("a120:a124"), Range("a149:a152"), Range("a177:a181"), Range("a206:a213"), Range("a239:a240"), _


View 4 Replies View Related

Copyrights 2005-15, All rights reserved