Home > Forum Home > Excel Portfolio Optimization Template > Run time error 438 Share

Run time error 438

Excel Help for Run Time Error 438 in Excel Portfolio Optimization Template


Forum TopicPost Reply Login

Run Time Error 438

Rate this:
(3/5 from 1 vote)
Angryis anyone can help me with this,when i run this program, this window appreas

microsoft visual basic
running time error '438'


then, i can only stop this program

THANK YOU~
 meilin
 Posted by on
 
Replies - Displaying 1 to 10 of 13Order Replies By: Most Recent | Chronological | Highest Rated
Fedup
Rate this:
(3/5 from 1 vote)
This could be due to the input data for the optimization. 

If you like, you can send through the template as a reply to the notification of this post and we can investigate this for you.

 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Hello, can someone please help..I am having the same problem when running the optimization.

"running time error '438'


then, i can only stop this program - PLEASE HELP!  Thanks, Lisa

 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
Which version of Excel are you using and how many products do you have in your Input sheet?
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(4/5 from 2 votes)
Version 2007 - with 4 products and 5 yrs price information for each
 Posted by on
Fedup
Rate this:
(3/5 from 1 vote)
We have made many tests with Excel 2007 4 products and 5 years and cannot replicate the error.  If you could reply to the notification email of this post with your template, then we can run it with your data and resolve this.

Thank you in advance.

 Excel Business Forums Administrator
 Posted by on
Shocked
Rate this:
(3/5 from 1 vote)
I am running these two macros in my spreadsheet in Excel 2007 and they run perfectly. However, my colleague sees a runtime 438 error when she tries to run it in Excel 2003. Any ideas? PLEASE help.

See my macros below:


Macro 1)

VBA Code:
Sub Sort()
'
' Sort Macro

    ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort.SortFields.Add _
        Key:=Range("U19:U80"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort
        .SetRange Range("O19:U80")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


Macro 2)
VBA Code:

Sub VOIbyCustomer()
'
' VOIbyCustomer Macro
'
    Application.Goto Reference:="Targeted"
    Selection.ClearContents
    Range("B18:I80").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "E8:E9"), CopyToRange:=Range("Targeted"), Unique:=False
  
      ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort.SortFields.Add _
        Key:=Range("U19:U80"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort
        .SetRange Range("O19:U80")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

End With
 
Range("N15").Select
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollColumn = 13


End Sub





 [email protected]
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
The Excel run time error 438 occurs when an method is not supported for the object in the VBA code. If a macro has been recorded in a later version of Excel, the object method may not be compatible with an earlier version.

In order to locate the exact problem, click on the Debug button when the error message appears and see which line of code is highlighted in the VBA project.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Hi Excel Helper,

Here's the specific line which Excel 2003 pointed out when  I clicked "debug" as you suggested to do --

 ActiveWorkbook.Worksheets("Value of Incentives by Cust").Sort.SortFields.Clear

Any idea what the issue might be? As I mentioned before the macro runs perfectly in Excel 2007.

Thanks much!
 [email protected]
 Posted by on
ShockedThe sorting functionality is more advanced in Excel 2007 and 2010 than Excel 2003 and prior.  For Excel 97-2003 simple sorting cannot be cleared as the data order is simply changed and the method is not stored in memory.

A filter can however be both set and cleared. To set a filter, sort on a column and then clear the filter, the following code (with comments) can be used.

VBA Code:
'Select the region
Sheets("Value of Incentives by Cust").Select
Range("O19:U80").Select

'Create a filter on the selected region
Selection.AutoFilter

'Sort a column - add more with Key2 etc
Range("U19:U80").Sort Key1:=Range("U19"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'Clear the filter (note that this is the same as setting a filter so if one exists it will clear it and if not it will add one).
Selection.AutoFilter
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
When I post the following code in VBA    "If Target.Worksheets("Sheet2").Column = 2 And Target.Worksheets("Sheet2").Row = 3 Then"
I get a run time error 438 prompt.  What do I need to do to avoid this error prompt when trying to specify which worksheet a row and column are located in?
 Posted by on
 Displaying page 1 of 2 

Find relevant Excel templates and add-ins for Run time error 438 in the