I convert text file into Excel. Then what I want is to look in column C for cell value “UNT:” (it is chopped word account: ) and copy value what is in the same row next column D into column K.
Then in column K copy down the value until next not empty cell.
My problem is that it works on Excel 97 and 200 but 2003 doesn’t work.
I have constructed a macro, (with lots of help from the fine people on this site), and have distributed it to several people in the company. The macro was written in Excel 2003, and runs perfectly on all the machines running Excel 2003, but persons using Excel 2000 are getting a runtime error, variable not defined.
Is there a trick to getting a macro to run in both versions of Excel?
I have a macro which is able to run very fast in Excel 2000. Almost 5s. When i run it in Excel 2003, it takes almost 4 to 5 mins to complete. Is there any patch in 2003 i need to run the macro smoothly?
View 4 Replies View RelatedI'm trying to run a macro on my machine (Windows 2000 and Office 2003) and I keep getting an error on one method. Now I tried to run the same macro on a different machine (Windows XP and Office 2003) and it didn't error out.
Public strControlTitle As String
Public strControlTime As String
Public strControlUnit As String
Public intControlItem As Integer
Public intLastNameOnly As Integer
Public intDataType As Integer
Sub Main()
Dim strControlItem, strPageName, strValue, sigma, z95, z99 As String
Dim r, c, count, cmax, rmax, rt, ct, low, high, a, b As Integer
Dim lesscol As Integer
With ControlChartForm
.ComboBox1.AddItem ("Average Turn Around Time All Patients")....................
I often need to use Paste Special . .. Values, using Excel 2003. So I recorded a Macro into "Personal.xls" and assigned a shortcut key combination "CTRL-Shift-V" so that it would always be available. I first select one or more cells and hit CTRL-C to copy to clipboard, click on another cell, then hit CTRL-Shift-V to run the following macro:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
It often works . . . and it often fails. When it is working it seems to keep working over and over. When it is failing it keeps failing. So it is almost as if there are two "modes" of operation, which for lack of terms, for this post I will coin the terms "fail mode" and "fixed mode". Once I am in one of the two modes, it stays there for a while. When it is in the "fail mode", I always get the dreaded:
Run-time error 1004: "Pastespecial method of Range class failed".
Since this problem is very intermittent, I believe this is why there are so many posts in the Internet about this, and so many people are replying back "it works fine for me". I have seen many solutions offered but none work so far for just a simple shortcut key to do a PasteSpecial :Values. Some solutions "work" . . . such as adding the PasteSpecial button to the Toolbar, or pressing ALT-E-S-V <Enter> - but both of these bring up the Paste Special dialog box which I want to avoid.
I spent days trying the many solutions offered but so far all I have found is a trick to convert Excel from "fail mode" to "fixed mode". The trick is to add a line to the macro to first do a "Paste Special: Format:
VB:
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Then select and copy any cell/s, click on another cell, and run the macro just ONCE - it will paste the formatted value successfully and you are now in the "fixed mode". Now you can remove the PasteSpecial:Format line that you just added, to get back to the original macro with just the "Paste Special: Values" line:
VB : Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Now Excel is in the "fixed mode" and the original macro will once again start working properly. BUT only for a while !! Then some time later, usually within a day or a couple hours . . . Excel goes back into that "mode" where once again the macro fails repeatedly. I have been unable to isolate what causes the mode to return to failure.
Any simple way to either change the code so that it ALWAYS is successful, why this often is not successful ? I believe that the fact that adding in the Paste Special : Format code and running it just once to fix the problem, also could be a clue as to what causes this problem to "sometimes" occur.
I have an annual leave (vacation) work book with a summary page work sheet and separate work sheets for each month i.e. Jan, Feb, Mar.....to......Dec. In order to protect the formulas I have protected them by allowing only access to the input cells on each work sheet and the protect each work sheet.
When someone either joins or leaves the team I have to manually unprotect each sheet and protect again when I have completed the amendments to each of the 13 tabs.
macro code I would need to unprotect all the works sheets in one go (as I use the same password for all the sheets) and reset the passwords (protect) the sheets with more macro code.
I will be running the two macros from my own personal.xls file and ideally they would be fully automatic i.e. I would not need to input the passwords in to unlock or lock the work sheets as the password would be written in the code already
Excel version 2003
Excel 2000 spreadsheet everything is fine.
When I open the same file in excel 2003 #NAME? Error appears.
I check Accept Labels in Formulas and it helped in some places but not everywhere.
I still have lots of NAME errors in a relatively simple spreadsheet.
I Excel 2000 everything works fine.
I have an activity recording tool created in Excel 2003 which works perfectly (ish)
However when some of my colleagues use it on machines running Excel 2000 it falls over at the following definition
Dim dlgFile As FileDialog
this is part of the following sub routine
Sub doCopy()
Application.ScreenUpdating = False
Call Archive
Dim dlgFile As FileDialog
Dim rv As Long
Dim sCreated As String
Dim sClosed As String
Dim wsThis As Worksheet
Dim wbkAOM As Workbook
Dim sThisCol As String
Dim aAOMCol As String
Dim strDate As String
Dim wsCore As Worksheet
Dim wsDiverted As Worksheet
Dim iRow As Integer
Set wsThis = Application. ActiveWorkbook.ActiveSheet
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
dlgFile.Title = "Select AOM Sheet"
dlgFile.InitialFileName = "*.xls"
rv = dlgFile.Show.......................
I have a Find Method routine in a procedure that works fine in xl2000, but fails in xl2003 with error code 9, subscript out of range.
Is there a different format for the Find Method in xl2003?
'code in xl2000
Dim CourseI As Range 'Students taking BusinessI
Dim CourseII As Range 'Students taking BusinessII
Dim BothCoursesTrue As Range
Dim Student As Range
'code
'code ...
For Each Student In CourseI
Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, LookAt:=xlWhole)
If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student
I recently launched a model that uses filtering on protected worksheets. The model was developed in Excel 2000, and everything works perfectly for the users who are also on 2000, but my Excel 2003 users get an error on opening the workbook.
The error asks for the password to unlock the sheet and is difficult to clear. Users have to hit Cancel 8 times to clear the error, at which point, the model appears to function perfectly. (There are 12 worksheets in the model, 2 with filters, all locked and password protected.)
The follwoing code is in the 'ThisWorkbook' module:
Private Sub Workbook_Open()
With Worksheets("Initiatives")
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
Because the error occurs when first opening the model but not at other times, I'm thinking that there's something in the Workbook_Open procedure that is causing the error.
When I open an excel 2000 spreadsheet in 2003, the formulas fields do not populate data just #N/A. I can see data just fine in Excel 2003 viewer.
View 5 Replies View RelatedI have this formula :
=SUM(F25:M25*$F$6:$M$6)
It works great in xl2003, and it works properly in XL2007 until you try and change it. I changed the column F to Column D and now it gives me a #value.
How can I re-work this for xl2007?
The below code works fine in excel 2003 but when I run it in excel 2007 it crashes excel.
(Microsoft Excel has encountered a problem and needs to shut down etc etc)
Private Sub CommandButton2_Click()
With Sheets("Sheet1")
.Range("A1").Value = ComboBox2.Text
.Range("C2").Value = ComboBox2.Text
.Range("C4").Value = TextBox5.Text
.Range("D1").Value = TextBox5.Text
.Range("C5").Value = TextBox7.Text
.Range("C6").Value = TextBox9.Text
.Range("C7").Value = TextBox13.Text
.Range("C8").Value = TextBox11.Text
.PageSetup.RightFooter = "&""Arial,Italic""&9" & ComboBox1.Text
End With
With Sheets("Sheet2")
.Range("G1").Value = ComboBox1.Text
End With
Unload Me
End Sub
I have tried all kind of changes that I thought might make a difference,
like changing .Text to .Value and vv,
This script works great in 2003 in formatting text brought from Access, but fails in 97 - unfortunately I don't have direct access to Excel 97 - can anyone with it (or with a compendious knowledge of the differences!)
View 9 Replies View RelatedI've developed a workbook with macros in Excel 2007 (Compatibility
Mode), Windows Vista Operating System. The purpose of the Macos
is to allow auto complete on a validated data entry field.
The macros run fine on this platform. I then copied the workbook
to my desktop, Excel 2000, Windows 2000 Professional Operating
System. One of the macros is killing Excel on this platform.
the macro works fine until it executes the paste values. At that point, the macro jumps to the "CountThem" function which is located in another workbook. The data that I am copy/pasting is in no way connected to any cells that are using that function. Although, other values in the workbook are passed down from data that uses that function.
I am still in the dark ages using Excel 2000.
This is the code for my macro.
Code:
Sub Current_to_Raw()
'
' Current_to_Raw Macro
' Macro recorded 2/12/2014 by
'
'
Range("N14").Select
[code]....
I have a very simple macro that copies data from one worksheet to the other. This macro worked fine in Excel 2007. I have since migrated to to 2013. The newly arisen problem is that when I run the macro, the data is not copied to the 'Previous Part Data' worksheet. However, if I step through the code line by line, it works fine.
Here is the code:
VB:
Sheets("Current Part Data").Select 'Select the 'Current Part Data' worksheet
'Copy the data to the 'Previous Part Data' worksheet then clear the data
Range("A4:V" & CurrentPartDataFinalRow).Copy
[Code]....
I have two questions:
1. Why did this work in 2007 and not in 2013?
2. Is there something I can do to get rid of the sheet selection statements to avoid cluttering my code?
I've sent the file to 2 different windows computers and it works fine, but doesn't work with the two macs that I've sent it to.
This is the code:
[Code] .....
In the following user defined function
Code:
Function CA2GT(ByVal S As String) As String
Dim x As Long, CA As Long, Total As Long
CA = InStr(1, S, "CA", vbTextCompare)
For x = CA To Len(S)
If Mid(S, x, 1) = "(" Then
'replace arguments: oldtext, start at number, number of characters, new text
[Code] .......
It works on one workbook but not another workbook. Why?
I've been searching around here to see if anyone had an answer about codes and macro changes from 03 to 07. The closest thing I found was something about lists being tables and such. However, I am still unsure of what the issue might be in my case.
Sub ArrangeColumns()
'
' ArrangeColumns Macro
' Macro recorded 3/7/2008 by ****
'
'
endRow1 = ActiveSheet.UsedRange.Rows.count + 1
Range1 = "A1:O" & endRow1
Range(Range1).Select
Range("A3:O39").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects("List1").Unlink
ActiveSheet.ListObjects("List1").Unlist
Columns("A:B").Select
Selection.Delete shift:=xlToLeft
Columns("I:I").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert shift:=xlToRight
Columns("M:M").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert shift:=xlToRight
Columns("B:B").ColumnWidth = 11.29
End Sub
So here, the red text is what gets flagged when you run the macro. I seem to be getting a Run-time error '9': Subscript out of range error.
I am not exactly sure what the macro does besides clean up and sort a portion of a worksheet. Also, there is not worksheet called "List1," but changing that value does nothing. Is there an equivalent command to those highlighted in red? Or, perhaps if anyone knows of a place where I may reference these commands myself, that would be great as well.
I'm using Excel to map automotive parts to vehicle applications (two different spreadsheets) for importation into a relational Access database. Production dates for parts rarely match the on-sale dates of the cars, so I've set up a macro that:
1. copies the start production date for a part record into the vehicle workbook at the top of the 'discontinued' column,
2. copies the end production date for the part into the vehicle workbook at the top of the 'release' column,
3. uses these pasted data points as criteria for the autofilter in the vehicle workbook as 'vehicle release date' < 'part end production date' and 'vehicle discontinued date' > 'part start production date'.
I find that the macro works perfectly in the autofilter for the vehicle discontinued date, but not for filtering the vehicle release date. The date is copied and pasted OK. When I check the custom filter dialogue box, the date has been entered in with the 'is less than' menu item selected, but it doesn't bring up any records unless you click on the 'OK' button once the dialogue box is open. I want to run the macro without having to run the autofilter manually at all. To troubleshoot the issue, I broke up the macro into two separate macros, one for release and the other for discontinued. Problem remains, even though the only difference between the two macros is the relative cell addresses. I've checked formatting of the cells for text vs. numeric and that doesn't appear to be a problem.
I programmed a simple macro (attached to a button) that scan the lines in the sheet and uses the function: Cells(y,2).EntireRow.Hide=TRUE (in order to hide certain lines). Usually, It runs fast without problem. But when I print out an area of the sheet, it cause the macro to slow down substantially. (when it runs again). In order to fix that , I had to close the file and reopen it again ( and not print out)
View 7 Replies View Related 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 using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.
Sub B_Test()
Dim myRange
Dim Results
Dim Run As Long
myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown))
Range("M3") = Application.WorksheetFunction.Sum(myRange)
myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown))
Range("D3") = Application.WorksheetFunction.CountA(myRange)
End Sub
I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.
I created a report that runs each weekday morning, using data from the previous workday.
I have written code that saves the file into a LAN directory as "BEST CASH MM-DD-YYYY" with the variables being the previous day's date.
My problems is that when I run the report on Monday morning using Friday's data, of course it's including Sunday's date and not that previous Friday...
Here's the test code I'm working with right now; I'm using a folder on my desktop until I get the code right, then I'll change it to the proper LAN directory:
ChDir "C:UsersC700MDesktopTEST"
ActiveWorkbook.SaveAs Filename:= "C:UsersC700MDesktopTEST" & "BEST CASH " & Format(Date - 1, "mm-dd-yyyy"), FileFormat:= xlOpenXMLWorkbook, CreateBackup:=False
How do you add a macro to a cell that works wen the user tabs out the cell?
View 9 Replies View RelatedI am trying to open a xls file and convert into csv. My macro works when I'm in debug mode. but If i run the macro (Not in debug) mode then After opening a file control is not going to next function. What is problem? Even I am not getting any error too
sub open file (FileName as string)
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim RowNo As Integer
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
'Set xlw = xlx.Workbooks.Open(SourceFolder + "" + FileName)
Workbooks.Open FileName:=SourceFolder + "" + FileName
' Columns("F:G").Select
' Selection.Delete Shift:=xlToLeft
Save_in_WDrive (FileName)
Set xlx = Nothing
end sub
I have this code working on my notebook:
Dim lastrow As Integer
Dim irow As Integer
lastrow = Workbooks("tmp").Worksheets("vRptMOMarkToHedgeFacilities"). Range("C65356").End(xlUp).Row
For irow = 2 To lastrow
Workbooks("tmp"). Sheets("vRptMOMarkToHedgeFacilities").Range("C" & irow).Copy
Windows("vlookupsheet.05.01.06.xls").Activate
Sheets("new stuff").Activate
Range("D" & irow + 1).Select
ActiveSheet.Paste
Next irow
Range("D1").Select
but it does not work on my desktop. For some reason when I go and execute the macro it gives me a "Run-time error '9' - Subscript out of range"
In Excel 2007 I am creating a macro with the "relative references" setting turned on. I want to repeat some tasks like: go to column A, sort, hide columns B thru F, hide columns H & I, return to column A.
Every time I try to create this macro by capturing key strokes, it inserts the workbook / worksheet name in the macro! Kinda defeats the ability to use it in any other workbook.
I am recording the macro in the PERSONAL.XLSX workbook. I have other macros that successfully perform in any workbook they are used in . . . but today I can't create one that doesn't incorporate the name of the original sheet it was recorded in.
I am trying to do a very simple Macro for merging data. My problem is that the macro works when I am stepping through in debugger, but does not work when I am in excel and I use the shortcut key (crtl + m) to run the macro. What happens when it doesn't work properly is it selects rows 2 and 3 and tries to paste them to the new workbook and I get an error saying the cells are not the correct type. I don't want it to do this because rows 2 and 3 are titles and have nothing to do with the data I am moving. As I said before, when I open VBE and step through the code it works just fine. The error only happens when there is no data on the initial work sheet.
View 10 Replies View Related