Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro in Excel 2003
I have been using the following code in Excel 2007 and it has been working
fine. I need for the code to work just as well in Excel 2003 but it runs slower and I get a Runtime Error 438: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wCtr As Long Dim w As Worksheet Dim myNames As Variant Sheets(Array("Carrier Rates", "Template")).Select Sheets("Carrier Rates").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("TEMPLATE").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("Template").Select ActiveSheet.Range("$A$1:$AC$3000").RemoveDuplicate s Columns:=Array(1, 2, 3, 4, 5 _ , 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), Header _ :=xlYes Sheets("Carrier Rates").Select ActiveSheet.Range("$A$2:$Y$3000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _ , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Header:=xlYes End Sub I am getting a Runtime error on "RemoveDuplicates" because it is not recognized in Excel 2003. Any suggestions to increase speed and eliminate the Runtime Error? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro in Excel 2003
I am not sure which workbook this is in, but try the following under the
assumption that the code is in the same workbook as the sheets you are attempting to adjust. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wCtr As Long Dim w As Worksheet Dim myNames As Variant ThisWorkbook.Worksheets("Carrier Rates").Range("2:3000").FillDown ThisWorkbook.Worksheets("TEMPLATE").Range("2:3000" ).FillDown ThisWorkbook.Worksheets("TEMPLATE").Range("$A$1:$A C$3000"). _ RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _ 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), _ Header :=xlYes ThisWorkbook.Worksheets("Carrier Rates").Range("$A$2:$Y$3000"). _ RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _ 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Header:=xlYes End Sub Note, anything that deals with either Select or Activate, I got rid of them as they are more of a headache to deal with than what it's worth. Those 2 methods should only be used if there is no other way to get around such issues which I do have one such case dealing with using a third party program and I have sent them the suggestions to fix their code, but they have yet to do it. If the worksheets is not in the same workbook as this code, then replace: ThisWorkbook with: Workbook("Book1.xls") where "Book1.xls" is the file spec name of the Excel file that contains the worksheets you are attempting to modify. Also, Excel 2003 does not have a "RemoveDuplicates" method on the Range Object (Or according to documentation, known as Range Property), so you would need to use the one of the recursive means rather it be: For Each cell in Range .... Next or: For I = # To # Step # .... Next I -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Coleman1" wrote in message ... I have been using the following code in Excel 2007 and it has been working fine. I need for the code to work just as well in Excel 2003 but it runs slower and I get a Runtime Error 438: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wCtr As Long Dim w As Worksheet Dim myNames As Variant Sheets(Array("Carrier Rates", "Template")).Select Sheets("Carrier Rates").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("TEMPLATE").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("Template").Select ActiveSheet.Range("$A$1:$AC$3000").RemoveDuplicate s Columns:=Array(1, 2, 3, 4, 5 _ , 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), Header _ :=xlYes Sheets("Carrier Rates").Select ActiveSheet.Range("$A$2:$Y$3000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _ , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Header:=xlYes End Sub I am getting a Runtime error on "RemoveDuplicates" because it is not recognized in Excel 2003. Any suggestions to increase speed and eliminate the Runtime Error? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro in Excel 2003
Ron thanks it does run alot faster in Excel 2007. I am still getting the
error the RemoveDuplicates is not supported in Excel 2003. Any suggestions? "Ronald R. Dodge, Jr." wrote: I am not sure which workbook this is in, but try the following under the assumption that the code is in the same workbook as the sheets you are attempting to adjust. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wCtr As Long Dim w As Worksheet Dim myNames As Variant ThisWorkbook.Worksheets("Carrier Rates").Range("2:3000").FillDown ThisWorkbook.Worksheets("TEMPLATE").Range("2:3000" ).FillDown ThisWorkbook.Worksheets("TEMPLATE").Range("$A$1:$A C$3000"). _ RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _ 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), _ Header :=xlYes ThisWorkbook.Worksheets("Carrier Rates").Range("$A$2:$Y$3000"). _ RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _ 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Header:=xlYes End Sub Note, anything that deals with either Select or Activate, I got rid of them as they are more of a headache to deal with than what it's worth. Those 2 methods should only be used if there is no other way to get around such issues which I do have one such case dealing with using a third party program and I have sent them the suggestions to fix their code, but they have yet to do it. If the worksheets is not in the same workbook as this code, then replace: ThisWorkbook with: Workbook("Book1.xls") where "Book1.xls" is the file spec name of the Excel file that contains the worksheets you are attempting to modify. Also, Excel 2003 does not have a "RemoveDuplicates" method on the Range Object (Or according to documentation, known as Range Property), so you would need to use the one of the recursive means rather it be: For Each cell in Range .... Next or: For I = # To # Step # .... Next I -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Coleman1" wrote in message ... I have been using the following code in Excel 2007 and it has been working fine. I need for the code to work just as well in Excel 2003 but it runs slower and I get a Runtime Error 438: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wCtr As Long Dim w As Worksheet Dim myNames As Variant Sheets(Array("Carrier Rates", "Template")).Select Sheets("Carrier Rates").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("TEMPLATE").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("Template").Select ActiveSheet.Range("$A$1:$AC$3000").RemoveDuplicate s Columns:=Array(1, 2, 3, 4, 5 _ , 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), Header _ :=xlYes Sheets("Carrier Rates").Select ActiveSheet.Range("$A$2:$Y$3000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _ , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Header:=xlYes End Sub I am getting a Runtime error on "RemoveDuplicates" because it is not recognized in Excel 2003. Any suggestions to increase speed and eliminate the Runtime Error? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Macro in Excel 2003
Excel 2003 does not have the method of "RemoveDuplicates", so to check for
duplicates, you would have to manually write a function (Note, in this case, it's a VBA function strictly to use within VBA, not a User Defined Function (UDF) that is used within formulas on the spreadsheet side. Though the creation of a such function is the same as the UDF, some of the restrictions that's in place for UDFs, it's not there for regular VBA functions.) that would check for the duplicates. A VBA function can be used to perform action and then return its result(s) to the method that called on. Some developers use "Function" more heavily than "Sub" just for this very reason. In your case, this would require recursive coding that check through the various cells. The one big warning with recursive coding, be careful as recursive coding can get caught in an infinite loop or take up excessive amount of time to process. If you ever used SUMIF or COUNTIF functions within Excel and noticed how much longer it takes to have the formulas calculate, exponentially speaking, as the number of rows increase within the source table as well as within the destination table, recursive coding has the same result. SumIf and recursive coding can take up excessive amount of memory as well, if not careful with their usage. -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Coleman1" wrote in message ... Ron thanks it does run alot faster in Excel 2007. I am still getting the error the RemoveDuplicates is not supported in Excel 2003. Any suggestions? "Ronald R. Dodge, Jr." wrote: I am not sure which workbook this is in, but try the following under the assumption that the code is in the same workbook as the sheets you are attempting to adjust. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wCtr As Long Dim w As Worksheet Dim myNames As Variant ThisWorkbook.Worksheets("Carrier Rates").Range("2:3000").FillDown ThisWorkbook.Worksheets("TEMPLATE").Range("2:3000" ).FillDown ThisWorkbook.Worksheets("TEMPLATE").Range("$A$1:$A C$3000"). _ RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _ 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), _ Header :=xlYes ThisWorkbook.Worksheets("Carrier Rates").Range("$A$2:$Y$3000"). _ RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _ 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Header:=xlYes End Sub Note, anything that deals with either Select or Activate, I got rid of them as they are more of a headache to deal with than what it's worth. Those 2 methods should only be used if there is no other way to get around such issues which I do have one such case dealing with using a third party program and I have sent them the suggestions to fix their code, but they have yet to do it. If the worksheets is not in the same workbook as this code, then replace: ThisWorkbook with: Workbook("Book1.xls") where "Book1.xls" is the file spec name of the Excel file that contains the worksheets you are attempting to modify. Also, Excel 2003 does not have a "RemoveDuplicates" method on the Range Object (Or according to documentation, known as Range Property), so you would need to use the one of the recursive means rather it be: For Each cell in Range .... Next or: For I = # To # Step # .... Next I -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Coleman1" wrote in message ... I have been using the following code in Excel 2007 and it has been working fine. I need for the code to work just as well in Excel 2003 but it runs slower and I get a Runtime Error 438: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wCtr As Long Dim w As Worksheet Dim myNames As Variant Sheets(Array("Carrier Rates", "Template")).Select Sheets("Carrier Rates").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("TEMPLATE").Activate Rows("2:2").Select Selection.AutoFill Destination:=Rows("2:3000"), Type:=xlFillDefault Rows("2:3000").Select Sheets("Template").Select ActiveSheet.Range("$A$1:$AC$3000").RemoveDuplicate s Columns:=Array(1, 2, 3, 4, 5 _ , 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), Header _ :=xlYes Sheets("Carrier Rates").Select ActiveSheet.Range("$A$2:$Y$3000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _ , 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25), Header:=xlYes End Sub I am getting a Runtime error on "RemoveDuplicates" because it is not recognized in Excel 2003. Any suggestions to increase speed and eliminate the Runtime Error? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2003 + macro help | Excel Discussion (Misc queries) | |||
Excel 2003 + macro | Excel Discussion (Misc queries) | |||
Excel 2003 Macro | Excel Discussion (Misc queries) | |||
Macro in Excel 2003 | Excel Programming | |||
Excel 2003 macro | Excel Programming |