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.
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.
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] .....
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.
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?
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)
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
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.
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.
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.
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
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.
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
Code: 'Paste the values into the word table Worksheets("Schedule").Activate Set MyTable = Union(Range("a120:a124"), Range("a149:a152"), Range("a177:a181"), Range("a206:a213"), Range("a239:a240"), _
Public No_of_Sims As Variant Public Sim_No As Variant Public Ground_Up_Agg As Variant Public UseSelfInsuredVehicle As String Public Option_Name(1 To 12) As String
[Code] .....
The code highlighted in BOLD is where I am getting runtime error 9 (Subscript out of range).
I get the subscript out of range error in the following code in the lines with set sk1 and set sk2. The problem disappears if I use the name of the workbooks (i.e. Workbooks("Sumy1") but I'd rather go with the variable which comes from the input box as the name may be different in the future.
Code: Sub Sumy()
Dim Sumy1 As String Dim Sumy2 As String Dim sk1 As Workbook Dim sk2 As Workbook
I am trying to copy from a text file to excel workbook... Thw work book have 3 sheets: Sheet1, Sheet2, Sheet3 Now i want my macro to write into row D of Sheet2...
Sub copy_txt_files() Dim FSO As Object, Folder As Object, file As Object Dim copyFrom As Workbook Dim wksCopyTo As Worksheet, wksCopyFrom As Worksheet Dim rngCopyTo As Range, rngCopyFrom As Range Set wksCopyTo = ThisWorkbook.Sheets(2) Set FSO = CreateObject("Scripting.FileSystemObject") Set Folder = FSO.GetFolder("C:logs") For Each file In Folder.Files If LCase(Right(file.Name, 4)) = ".txt" Then Set copyFrom = Workbooks.Open("C:logs" & file.Name) Set wksCopyFrom = copyFrom.Sheets(1) Set rngCopyFrom = wksCopyFrom.Range("D1") Set rngCopyFrom = wksCopyFrom.Range(rngCopyFrom, _ rngCopyFrom.SpecialCells(xlCellTypeLastCell))...............
I used the macro recording thing to record a code to do OLS regression, added a few things and put it in a bigger program, but it's not working. It gives me a "Subscript out of range" message.
Sub button3_click() Dim iRow As Long Dim ws As Worksheet Set ws = Workbooks("USA Appliance RepairUSAAR Phone Contacts DB.xls").Worksheets("Data")
' find first empty row in database iRow = ws. Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'copy the data to the database ws.Cells(iRow, 1).Value = This.ContactID.Value ws.Cells(iRow, 2).Value = This.scheduledate.Value ws.Cells(iRow, 3).Value = This.takenby.Value ws.Cells(iRow, 4).Value = This.calldate.Value....................
Nothing complex, right? Well I am getting the error that is in the title of this message, and it highlights that third line, where I am setting the database. How can I fix this? Why does it say subscripts when there isn't anything with subscripts in it in the line?
I cant seem to fix this runtime error. I believe i have got it down to the exact line which i marked with (PROBLEM). Can you help me out?
Sub Search()
Dim Filename As String Dim Search As String Dim Result As String Dim Resultant As String Dim intCount As Integer Dim strOut As String Dim lngLoop As Long Dim Link As String
I have a Userform which has 2 textboxes. User enter his employee id on textbox1 then enters a valid break time code which are 1,2 and 3 on textbox2. If User typed 1 then first breaktime start time will be pasted on the worksheet. When User returns after break he then enters his employee id again and then type 1 to end his first breaktime, the end time will be pasted on the worksheet. My program works fine however, I am trying to place a code which will prompt the user to end his first break time before entering another valid break code. Kindly check my code below as I am receiving an error message. 'Run-time error 9' Subscript out of range. This thing is driving me nuts. Any advise or can you tweak my coding vba experts.
My macro adds a sheet to an existing Excel workbook. However, this only works the first time. If I delete the added sheet and run the macro again I get the above error. The macro below call a second macro (see later )
I am trying to allow a user to select a file and have the only sheet from that file added to the workbook they are working in. When I run this code I get a "Subscript Out of Range" error
Code: Sub importRawData() Dim rawDataSheet As String MsgBox "Please select the unmodified AR Aging Report exported from PFW", vbOKOnly rawDataSheet = Application.GetOpenFilename(FileFilter:= _ "Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Select File") Sheets.Add(Sheets("PWF AR Data"), , , rawDataSheet).Name = "PWF Raw Data" End Sub
I have the following code which performs a different action depending on whether K1 says "true" or "false". K1 contains a formula which identifies whether or not E1 is already present on a list on another page. If K1 says "false," the code is supposed just reenter the formula that was previously in E1 (because I don't know how to tell it to do nothing). If K1 says "true," I want a popup box where answering yes reenters the previous formula and answering no pulls up an input box to enter other data. This code worked fine until I closed the workbook. When I tried to open it again, I got a runtime error 9: subscript out of range message and nothing happens when I change the value in A1 (upon which the formula in E1 is based).
Private Sub Worksheet_Calculate() Application.EnableEvents = False If Sheets("Mix Designs Input").Range("K1").Value = "true" Then Range("E1").Select Ans = MsgBox("Do you want to overwrite this mix design?", vbYesNo) If Ans = vbNo Then Title = InputBox("Enter the name of the design you wish to input") Range("E1") = Title If Ans = vbYes Then ActiveCell.FormulaR1C1 = _ "=IF(ISBLANK(RC[-4]),"""",IF(ISERROR(VLOOKUP(""reclaim"",R[8]C[-4]:R[13]C[-
