1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel macro 2003 to 2010 error 438

Discussion in 'Business Applications' started by George66, Feb 20, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. George66

    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:
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Hi Goerge,

    Welcome to the forum.

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

    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
     
  4. George66

    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:
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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
     
  6. George66

    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
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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?
     
  8. George66

    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
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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
     
  10. George66

    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
     
  11. Garf13LD

    Garf13LD

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

    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
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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
     
  14. George66

    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
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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
    
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090398

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice