Run-time Error 9 When Upgrading To 2007
Feb 18, 2010
Our office has recently upgraded from Excel 2003 to Excel 2007, and now I am having an error message with my workbook. The error is as follows:
Run-time error '9':
Subscript out of range
I believe I have all the macro security settings set up, however, I think this is a problem with a printing macro I had help with here. It was created to print the number of pages entered into a specified cell when the Print button was clicked - I think 2007 uses a slightly different button called 'Quick Print'? I'm just guessing here - but when I hit the Quick Print button, the Print Options box appears instead of immediately printing.
View 14 Replies
ADVERTISEMENT
Apr 1, 2009
I have some schedules set up with multiple formulas and macros (originally setup in 2003) however when attempting to make this a 2007 file when I go to copy a sheet to create another schedule I get the message “A formula or sheet you want to move or copy contains the name “xx”, which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click yes. To rename the range referred to in the formula or worksheet, click no, and enter a new name in the Name Conflict dialog box.”
View 2 Replies
View Related
Aug 11, 2008
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:
Run-time error '429':
ActiveX component can't create object
and is stopping on the line
Set OutApp = GetObject(, "Outlook.Application")
FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String
'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
View 9 Replies
View Related
Sep 14, 2007
I was using the following macro on Excel 2000, 2002 & 2003 for many years without a fuss. Recently, I upgraded to Excel 2007. When I run the macro now, I get the following error message:- Run-time error 445 Object doesn't support this action.
Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Documents and SettingsDesktopCommercial Database"
.SearchSubFolders = True
.Filename = "*.*"
.TextOrProperty = "BANK"
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
End If
End With
End Sub
Additional info:- The 1st line of my code which is With Application.FileSearch is highlighted in yellow when I run this macro.
View 2 Replies
View Related
Feb 20, 2009
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View 9 Replies
View Related
May 9, 2006
Haven't been here in a long time ...
Anyway, I got a HUGE (in terms of code size) wbk that is sort of a management report writer that connects to external data bases and does lots of other stuff.
Due to the way this wbk was designed the actual reports the client requested are in it as well. So now when i need to upgrade the code i hafta use a small proc that kills all the code in the prev version of the wbk but keeps the sheets (ie reports themselves) intact, and then recreates new code modules classes etc from the upgraded version of the wbk.
Although this works for now i really want to improve the code maintenance so i want to implement dll's into this project.
The wbk itself needs to have only basic code to call dll's and some other stuff and that's it. The code itself should come in dll's into a certain folder and wait for the next upgrade . How can i do this?...
View 5 Replies
View Related
Feb 7, 2007
How do create a formula that will only execute this formula:
=COUNTIF($F$7:$F$106,"0")
for the rows in the range g7:g106 that are equal to 12 or 13 or 14 or 15
example
f7=0
f8=1
f9=0
f10=0
g7=12
g8=12
g9=17
g10=14
result = 2, because f7 and f10 are the only matches
View 9 Replies
View Related
Jun 23, 2014
I have multiple spreadsheets that have formulas pulling from other spreadsheets that I use for daily, monthly & annual reports. After upgrading to Windows 7 from Windows XP these spreadsheets are no longer seeing the formulas if I open multiple spreadsheets at a time. This is a problem as my monthly and annual spreadsheets need to pull from multiple workbooks in order to give me monthly and annual totals. I have macros set at "Enable all macros" and have added the file location in "Trusted Locations". What else do I need to set in order to work on multiple spreadsheets at a time and the formulas pull data as needed?
View 1 Replies
View Related
Mar 8, 2014
production01.png
I am trying to figure a way to search for a cell that has a specific date and time range. There are several cell titles pending on the activity. I want to find a cell that has a time ** 7:30-15:30 , 15:31-17:30, 17:31-20:30. The end result is to calculate the activity between those time periods based on the data cells.
Example
If the date searched time field ** the activity ranges is 1635 I need to split the time and credit the activity time in the 730-1530 time and the rest on the 15:31-17:30 time
I have been able to do it on a single labor group based on time alone, but when I try to add the DATE to it my numbers go null. Eventually i will need to add 11 labor groups daily for weeks at a time .
Excel 2007
View 3 Replies
View Related
Oct 8, 2008
I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:
'Run-time error '91':
Object variable or With block variable not set'
Do I have to clear a buffer after each cells.find search?
My
View 7 Replies
View Related
Oct 10, 2006
I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time.
Every time I get the error, it highlights this line of
Sheets("Regenerate Request").Paste
This is all of the code up to where I get the error:
Sub YesRegen()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email
Application.Run "LogUnprotect"
Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy
'Selection.Copy
Sheets("Regenerate Request").Activate
Application.Run "RegenFormUnprotect"
Range("A40:K40").Select
Range("A40").Activate
Sheets("Regenerate Request").Paste
View 10 Replies
View Related
Mar 5, 2013
I have a relatively complex report that I work with and a worksheet is no longer required. I have deleted the worksheet and reference to it hwoever when running the macro to pull all the data, it gets to the summary of all the data and i get the Run Time Error 1004 Application-defined or object-defined error pop up. ON reviewing it, it is on this line ActiveCell.Offset(0, 0).Range("a1:a" & Range_Height).Select of the below code...
VB:
Sub GetRangeName()
Sheets("TOTAL").Select
[Code].....
use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.
View 1 Replies
View Related
Apr 26, 2009
I have a simple function below to put in different forumlas in different cells to get stock quotes. When I run this I get runtime error 1004 application-defined or object-defined error. The first formula goes through but vba chokes on the next formula: ActiveCell.Offset(I - 1, 4).Formula = username
View 10 Replies
View Related
Aug 12, 2009
Run-time error '1004' Application-defined or object-defined error. I am trying to use this
View 2 Replies
View Related
Oct 4, 2009
I was trying to use the below code
View 4 Replies
View Related
Aug 17, 2008
For some reason my form won't open when the workbook is opened. I get an error message "run time error '424' object required" (which happens when I have Form1.show in the BOTH workbook_open event and the userform_initialize event (oops)). When I removed form1.show from the userform_initialize I don't get an error but I also get no form. I recall having this issue before but I can't recall how to fix it.
Can I call the userform_initialize event from the workbook open event to get around this successfully and properly?
View 9 Replies
View Related
Dec 4, 2009
I keep getting a Run Time Error 1004 (Application Defined or Object Defined Error) when my sub reaches this line:
ActiveCell.Formula = "=SUM(D222,D224,D226,D227,D229,...)"
In the actual line of code the "..." above is another 20-30 or so cells in column "D". Probably no more than 150-170 characters in the line.
If I remove half of the cell range names it works, but I need all of the cell ranges for the equation.
View 9 Replies
View Related
Jun 20, 2008
I have a macro that searches through a certain location for Excel files. The program and files were made in Office 2003. When another user runs the macro in Office 2007, the files are not recognized or the macro does not search, I'm not sure. Either way, the files are never located in their folder location. Any thoughts or ways around this?? The top part of the code is posted below,
Do
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:Documents"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = " Book*.xls"
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count ' Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
View 3 Replies
View Related
Jan 8, 2009
I have written a very basic Macro code to retrieve a photo off of my server. I want it to run this macro when a person has selected a certain item off of a drop down list. The problem I am having is that when I try and run the macro manually or when the workbook opens I get a an error message that reads "Compile Error: Invalid outside procedure". I click "Okay" and it highlights this portion of the code
View 5 Replies
View Related
Nov 7, 2012
I am using Excel 2007. I am having problem with web query. I want to get data from Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database this website. In excel 2007-> get external data tab->from web-> and i typed the website(Komatsu parts catalogue, komatsu parts book, komatsu parts search, komatsu parts numbers, komatsu parts database) and click go then i receieved Script error
[An error has occured in the script on this page]
Line: 3
Char: 1373
Error: Expected identifier, string or number
Code: 0
[URL] .....
Do you want to continue running script on this page?
yes no]
like that i received an error
View 1 Replies
View Related
Aug 13, 2014
How can we "read" information from the VBE Error Trapping options (Break on All Errors/ Break in Class Module/ Break in Unhandled Errors) programmatically?
Background: I am developping a spreadsheet that needs to be sent around to distant users. Some of them might not have the default "Break on Unhandled Errors" setting), which would just make my programme fail or risk to fail...
This issue has been covered in the thread below, but the proposed solution doesn't work on my side (always returning the same output!): [URL] .....
Hint: it has been suggested to look into the registry, which the linked post does.
Ideally, I would love to be able to change the setting programatically, but that seem near to impossible in excel 2007 VBA (unless calling an external programme that closes and restarts excel after having changed the option?...)
View 10 Replies
View Related
Jan 9, 2013
(Excel 2007), I have a template that I need duplicated for every reference. My Worksheet lists all the references and the macro use to duplicate a worksheet for every reference in the worksheet. The problem is when duplicating now, it duplicates the name of the template. For example, when the macro is ran sheet1= Template(1), sheet2= Template(2) ect.
Here's the code- I think it has something to do with the named ranges
Code:
Sub Macro1()
For i = 1 To Application.WorksheetFunction.CountA(Worksheets("Worksheet").Range("A:A"))
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Worksheets("Worksheet").Cells(i, 1).Text
Next i
End Sub
View 4 Replies
View Related
Feb 19, 2014
How to get the "Expected date and time of closure" in MS Excel 2007
Call No. ABCD
Login Date 2/15/2014
Login Tme 9:30 AM
Agreed SLA (Service Level Agreement) 1hr
Accessibility Type 24hrs
Site Accessibility 24hrs
Accessibility Days All Days
ETC Date ?
ETC Time ?
[Code] ......
View 1 Replies
View Related
Jul 20, 2012
I want to calulate time by decimal the problem is anything after 1 am wont work
example start at 12:25 finsh at 1:45 time should show up as 1.33
using excel 2007
View 3 Replies
View Related
Aug 29, 2007
I have some excel tools developed in Excel 2003 which uses the date and time picker control (DtPicker). When I open my files in Excel 2007 the DtPicker is missing and the change_event will not load the calendar. I cannot find and activate the datepicker in the Reference base.
View 6 Replies
View Related
Mar 5, 2012
I am running excel 2007 thin client. in attempting to refresh a query, I have encountered this error message: "run time error 1004 Incomplete datasource". The four line macro is as follows:
Code:
Sub RefreshFamily()
Sheets("Family").QueryTables("Family_refresh").EnableRefresh = True
Sheets("Family").QueryTables("Family_refresh").Refresh BackgroundQuery:=True
Sheets("Family").QueryTables("Family_refresh").EnableRefresh = False
End Sub
Where the named range "Family refresh" is defined as :
Code:
=OFFSET(Family!$A$1,0,0,COUNTA(Family!$A:$A),COUNTA(Family!$1:$1)-2)
Why the macro thinks that my named range does not exist?
View 1 Replies
View Related
Aug 9, 2012
I am getting a "Run time error 5", "invalid procedure call or arguement" when I run a macro on a PC other tha the one on which it was created.
In this case both PC's are running on the version 2007 of Excel.
This is the highlighted statement when the macro fails
Code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"SOCX25!R1C1:R" & Lr & "C23", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="SOCX25!R7C25", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
View 4 Replies
View Related
Nov 3, 2012
Excel 2007
No empty rows
No empty cells
No calculations in cells
Field names look fine - no punctuation no merged cells
If I select one column - any single column I can generate a pivot table but not with multiple columns
View 4 Replies
View Related
Jul 18, 2013
We are using Windows7 with Excel 2007. We have old code from excel 2003 and earlier that was adding a msoControlPopup with other sub-controls using the code below:
Code:
Set HelpMenu = CommandBars(1).FindControl(Id:=30010)
If HelpMenu Is Nothing Then
' Add the menu to the end
[Code].....
I tried going to Excel Options>Customize>Reset to reset any customizations to the quick access toolbar as this was recommended by something else I found online. This did not fix the problem.
I can find the control in the Immediate window but it just isn't showing in the add-ins tab. if I can find and repair that XML file perhaps?
View 9 Replies
View Related
Jan 12, 2012
Adding items to combobox at run time
Setup office 2007 win xp pro ie8
Further to my post relating to swaping jpgs
The code below scans from the column on the row of the worksheet till it finds a blank, this seems to work ok as I previously had a msgbox in that displayed the value of n once the loop finished
So I was hoping that the values in the columns on that row would be added to the combobox but for some reason I get a typing missmatch error.
Code:
Dim n As Integer
n = 1
Do While LValue6 "" 'find out how many alternatives there are
LValue6 = FoundISBN.Offset(0, 32 + n).Value
n = n + 1
DataInput.ComboBox1(n) = LValue6 - I was hoping that the value in the cell was added to the combobox
Loop
View 4 Replies
View Related
