VB Works On One PC, Not On Another (Run-time Error 9 )
Apr 15, 2009
I run PCs at work and at home. Both machines run Windows XP and Office 2007
Home PC: Windows XP Home edition Version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO
Work PC:Windows XP Professional version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO
The VB was created on the home PC. It works. On the Work PC it crashes on the line with bold. Run-time error 9 - Subscript out of range. This have never happened before (or with any other VB routine). I also changed fileNew to include the full path, but same error. What has caused this? I suspect it’s the 'fileNew'.
This is a function to add the ascii values of a string to give a single value. Initially I wrote this as a Sub routine and it worked fine, but when making it into a function, I get this error. From a little research it appears that you can not use a string as an argument for a function call, yet that defeats the object of this particular function.
The intention of this function is that it works on a name in a given cell and the value (an integer) that is produced is then displayed in another cell, or if using it as a formula, the cell the formula is in.
I simple wrote the work "Hello" in cell A1 the ascii values of these added ignoring any spaces give 500 which is correct.
I am trying to programmatically select an item from a single selection listbox (i.e., set to fmMultiSelectSingle). The following code works properly only every other time I run it; the problem is extremely repeatable.
Const storeStartCell = "B5000" Private Sub ListBox_CounterTOs_Click() selectedTOName = CStr(Mid(Worksheets("Sheet1"). Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 3, 3)) selectedTONumber = CInt(Mid(Worksheets("Sheet1").Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 6, 4)) ListBox_TO_Name.Value = selectedTOName ListBox_TO_Number.Value = selectedTONumber End Sub
The times that it works, ListBox_TO_Name.Value is set to something like "ABC" and ListBox_TO_Number.Value is set to something like "1234". When the values get set properly, the associated click subroutines (e.g., ListBox_TO_Name_Click()) get called automatically when the value is set. The times that it doesn't work, they are both set to "" and the click subroutines do not get called. I also tried
I created three macros to work with reporting for my company.
One sorts a field, another deletes unwanted columns, and the third double checks columns for answers--I work for a telecom polling firm.
It was working last week on my computer, and it still works on another computer at the office. However, I keep getting an error message when I try to run it.
The message is "Compile Error: Expected Function or Variable". The code is below, and bolded where it breaks down.
--selection.Autofilter
[Code] .......
What could be the rationale for the sudden breakdown? I was testing it on a file that I have tested with before without issue. I also tried a second file, and even restarted my computer. I am currently trying to write a fourth macro for counting responses and giving percentages, so while I don't need to have the perfectly cleaned data to do so it would be nice to have.
I'm working on a sheet which uses a fair amount of indirects, deleting or adding rows bugger these up, so I wanted to replace them with a macro which does if for them and keeps the structure intact. I've sorted the macro to do this and have sorted the macro to replace delete with my delete in the cell RC menu but the row RC menu only seems to work on around 50% of the PC's? All PC's are XP with XL 2003. The code I've used is detailed below. Is this a known issue with XL or something funky with some of the PC's?
With Application . CommandBars("Row").Controls("Delete Row").Delete Application.CommandBars("Row").FindControl(ID:=293).Delete Set cBut2 = .CommandBars("Row").Controls.Add(Temporary:=True, Before:=6) End With
With cBut2 .Caption = "Delete Row" .Style = msoButtonCaption .OnAction = "Delete_Row" End With
Sub RemoveCodeAndSave() 'Remove all code from ThisWorkbook code module ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
[Code]....
I get the error "Run-time error 440: The specified dimension is not valid for the current chart type" why I am getting this. The macro does everything I want it to, except for throwing the error at the end.
I have a macro that works by pasting formulas into cells and then pasting over them with the values produced by the formulas. It works fine for 500 iterations then crashes at this line:
Ok, It works EXCEPT for when F3="EX" then I get the NAME error thing. I need it to work that If F3 is Ex AND yes then it is E3+17 but if it is just Ex then E3+3
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?
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
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.
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
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?
I have the following code in a macro and when it is run I get a Run Time Error 438 Object doesn't support this property or method. This occurs at the first occurrance of the destination/source.
I have the main form completed and everything appears to be in order. So I made a button on the first sheet that simply calls for the main form to be shown. However, every time I click it, I get the run time error 424 object needed thing. I don't understand because the button is calling the form and the names are all correct. When I click debug, it takes me to the small code for the start button. Below are the codes for the start button and the main form.
In my mind everything checks out, but I am still very new to all this.
Here is the 'Shifts cells to make space for inserted VFD Dim CheckBoxes As Integer Dim UpLeft Dim LowRight Dim CountCells As Integer Dim MoveTo As Range
I'm gathering and ordering data. It's in the biotech field so I have to work with a lot of reaction formulae and compound abbreviations. Alas those are not standardized. So I have gathered all the compounds used by different organisms and standardized their abberviations and I want to replace the abbreviations used in the reaction formulae of the organisms with the standardized abbreviations so I can compare the formula with each other.
This resulted in a list of formulae, and a list of 2 columns, 1 the old abbreviations and the other the new abbreviations. I wrote a macro to pick an abbreviation out of the old abbr column and find it in the formula list. Then the old abbreviation should be replaced with the new abbreviation.
I got it working but the problem is that some abbreviations are longer than others and thus the smaller ones may resemble letter combinations in the larger ones. This can easily be solved by sorting the abbreviation list so that the largest abbreviations are on top and will be checked first. However, after I did this the macro didn't function any more. I have no clue what to do.
I am getting a run time error and highlighting this section of code.
The entire code I am using is below. This has worked fine on my maching now trying to use it on another exact machine I am getting this error.
The way the code works..
It navigates to a webpage that allows the user to download the data which is called DownloadNCPartListServlet.
Windows("DownloadNCPartListServlet").Activate I think I may need to tweek the .xls file extention properties. What do you think?
Public Sub SKPIUPDATE() Dim QPR Dim lnk Dim frm Dim start Dim fin Dim drp1 Dim drp2 Dim src1 Dim NAMC As Integer ' This macro will automatically open and download the TMMK-VEH daily scrap 'and store the file in the same directory
Set QPR = CreateObject("InternetExplorer.application")
'Changes labels from zip code entry Private Sub Textbillingzip_change() Label42.Caption = Sheets("lists").Columns(10).Find(Textbillingzip.Value).Offset(0, 1).Value Label43.Caption = Sheets("lists").Columns(10).Find(Textbillingzip.Value).Offset(0, 2).Value End Sub Private Sub Textsetupzip_change() Label44.Caption = Sheets("lists").Columns(10).Find(Textsetupzip.Value).Offset(0, 1).Value Label45.Caption = Sheets("lists").Columns(10).Find(Textsetupzip.Value).Offset(0, 2).Value Label46.Caption = "Zone " & Sheets("lists").Columns(10).Find(Textsetupzip.Value).Offset(0, 3).Value End Sub I would like to have some error message pop up (or something else) rather than the current Run Time '91' error when the imput from either textbox is not found on the sheet "Lists"
I've written this macro to get data from a .csv and import into my spreadsheet however I get an intermittent "400" error, when I get the msg it says "method 'range' of object' _global failed".
Sub get_confirm_file_from_inbox()
Dim strPath As String Dim strFile As String Dim wbname As String On Error GoTo Errorcatch wbname = ActiveWorkbook.Name strPath = "C:xxxManageCentralinbox" strFile = Dir(strPath & "confirm*.csv") Do While strFile "" Workbooks(wbname).Activate Sheets("Confirm_Data").Activate Range("A3:BK").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.ClearContents...................
