Solved: Excel macro 2003 to 2010 error 438

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
I have a macro I wrote in excel 2003 (by recording small steps as a chain of macros), I just upgraded to a new machine and Office 2010.

When I try and run the macro it comes back with error message "Run time error 438, Object doesn't support this property or method."

When I run debug the code line that is flagged is:

Selection.End(X1 to Left).Select

Are there any commands or options which have changed between 2003 and 2010 and is there a conversion list anywhere?

George intermediate user running Windows 7 64bit, Office 2010

:confused:
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hi Goerge,

Welcome to the forum.

The syntax you show seems wrong and throws the error.
Have you tried is on 2003 again?
 

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
I don't have access to Excel 2003 any more. The line in my post was retyped by me, but here is a copy of the sub routine.

Sub CONCILE_STORE_CURRENT_DATE()
Selection.End(xlToLeft).Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(440, 0).Range("A1").Select
ActiveSheet.Paste

When I go into debug the second line is highlighted in yellow

George
 

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
I think a couple of explanations are in order

As I said in my opening, The macro is composed of several routines.

One called Concile which is made up of 13 subroutines chained together, when run on its own it works fine, but when I run a larger routine called newbook, it runs the first two subroutines fine, but when Concile is called by the larger routine it hangs on the first line of the concile code despite having run fine on it's own.

Concile This runs fine on it's own

Sub CONCILE()
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_STORE_CURRENT_DATE"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_CREATE_HOLD_PLACE"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_UNHIDE"
Application.CutCopyMode = False
Application.Run MACRO:="CASHFLOW.XLS!Module1.CONCILE_CLEAR_1"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_COPY_HEADERS"
Application.CutCopyMode = False
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_COLLECT_UNRECONCILED"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_GET_BALANCE"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_INSERT_HEADER2"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_RESTORE_FORMULA"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_PLACEMENT"
Application.Run MACRO:="CASHFLOW.XLS!CONCILE_UNBORDER"
End Sub
.
Newbook this macro crashes when concile is run here.

Sub Newbook()
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Delete_Old"
Application.Run MACRO:="CASHFLOW.XLS!NewBook_Copy_Old"
Application.Run MACRO:="CASHFLOW.XLS!Concile"
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Unhide"
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Strip"
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Move"
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Get_Balances"
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Style"
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Get_U"
Application.Run MACRO:="CASHFLOW.XLS!Newbook_Close_workings"
Application.Run MACRO:="CASHFLOW.XLS!PAINT_STYLE"
Application.Run MACRO:="CASHFLOW.XLS!GO_To_Home"
End Sub

I am not very experienced so forgive me if I use the wrong terminology.

George Humphries
:confused:
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Code:
Sub CONCILE_STORE_CURRENT_DATE()
   [COLOR="Red"] Selection.End(xlToLeft).Select[/COLOR]    
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(440, 0).Range("A1").Select
    ActiveSheet.Paste
Nothing selected so it thorws the error
 

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
Why does it work when I run the macro concile on it's own, but not when I call it from within a bigger macro?

George
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
What is the last command of the 'bigger macro' is the starting situation the same as when you trigger the macro on it's own?
 

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
The small macro just before Concile is run is.

Sub Newbook_Copy_Old()
Application.Goto Reference:="R1C1:R434C8"
Selection.Copy
Application.Goto Reference:="Old_Book!R1C1:R434C8"
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
Application.Goto Reference:="CASHFLOW!R3C1"
End Sub

George
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Here's the error

Selection.End(xlToLeft).Select

You cannot go left if the last selection is column 1, (Application.Goto Reference:="CASHFLOW!R3C1") you can't go to column 0
 

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
Tried making it column 2 it still hangs.

But if I step into the larger macro and then use F8 key to step into each line it runs perfectly.

I don't know what's going on???

George
 
Joined
Apr 17, 2012
Messages
455
Try
worksheets("CASHFLOW").range("A3").select
instead of
Application.Goto Reference:="CASHFLOW!R3C1"
 

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
Tried worksheets"cashflow").range("A3").select

It comes back

Runtime error 1004
Select metod of range class failed

George
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hi George,

I hope you go the syntax correct: worksheets("CASHFLOW").range("A3").select

instead of worksheets"cashflow").range("A3").select

And very important is the worksheet the active sheet? If not the select statement returns an errror
 

George66

Thread Starter
Joined
Feb 20, 2013
Messages
12
Yes to both, I cut and pasted the command.

The worksheet is the active sheet.

George
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Well, and did you change the syntax?

Code:
Sub CONCILE_STORE_CURRENT_DATE()
    Application.CutCopyMode = False
sheets("CASHFLOW").Activate
range("A3").select
    Selection.Copy
    ActiveCell.Offset(440, 0).Range("A1").Select
    ActiveSheet.Paste
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Members online

Top