+ Reply to Thread
Results 1 to 7 of 7

Object doesn't support...on certain computers

Hybrid View

  1. #1
    Registered User
    Join Date
    05-11-2016
    Location
    Dallas, TX
    MS-Off Ver
    excel 2016
    Posts
    18

    Object doesn't support...on certain computers

    Dear All,
    I am a self taught VBA user and I'm sure my code is inefficient and sloppy but I usually end up getting it to do what I want. I've run into a snag and I'm completely lost. The following macro is a called macro that runs as intended on my machine. When my boss tries to run it (it's a tool for him) he gets the following error: "Object doesn't support this propery or method". I've run it step by step on his machine and I think I've identified where it fails.

    (FYI I am using Excel 2016 and he's using Excel 2013)

    ***UPDATE - I tried it on another coworker's machine with Excel 2016 and it worked find there as well

    ***UPDATE #2 - It also worked on a coworker's machine with Excel 2019.

    Thanks in advance for your assistance.

    Sub Sort_JobCosting()
    Dim wB As Workbook
        Dim pE As Worksheet
        Dim cC As Worksheet
        Dim cA As Worksheet
        Dim cM As Worksheet
        Dim rO As Worksheet
        Dim jC As Worksheet
        Dim x As Integer
        Dim y As Integer
        Dim z As Double
        Dim userResponse As String
        Set wB = ThisWorkbook
        Set pE = wB.Worksheets("Paste Expense")
        Set cC = wB.Worksheets("Control Center")
        Set cA = wB.Worksheets("Calculator")
        Set cM = wB.Worksheets("CURRENT MONTH")
        Set rO = wB.Worksheets("Roster")
        Set jC = wB.Worksheets("Job Costing")
        x = 4
        y = 2
        z = 0
        jC.Select
        
    Columns("A:I").Select
        jC.Sort.SortFields.Clear
    'the next line is where it fails
        jC.Sort.SortFields.Add2 Key:=Range( _
            "E2:E1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        jC.Sort.SortFields.Add2 Key:=Range( _
            "A2:A1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        jC.Sort.SortFields.Add2 Key:=Range( _
            "F2:F1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With jC.Sort
            .SetRange Range("A1:G1200")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Do Until jC.Cells(y, 1) = ""
    jmp:
            If jC.Cells(y, 1) = jC.Cells(y + 1, 1) And jC.Cells(y, 5) = jC.Cells(y + 1, 5) And jC.Cells(y, 6) = jC.Cells(y + 1, 6) Then
            z = z + jC.Cells(y, 4).Value
            y = y + 1
            GoTo jmp
            Else
            z = z + jC.Cells(y, 4).Value
            Rows(y + 1).Insert shift:=xlDown, _
            CopyOrigin:=xlFormatFromLeftOrAbove
            jC.Cells(y + 1, 3) = "Total:"
            jC.Cells(y + 1, 3).HorizontalAlignment = xlRight
            jC.Cells(y + 1, 3).Font.Bold = True
            'If z = 0 Then z = jC.Cells(y, 4).Value2
            jC.Cells(y + 1, 4) = z
            'jC.Cells(y + 1, 4).NumberFormat = "0.00"
            jC.Cells(y + 1, 4).Font.Bold = True
            y = y + 1
            End If
        z = 0
        y = y + 1
        Loop
              
    End Sub
    Last edited by gcerulli; 10-22-2019 at 04:44 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Object doesn't support...on certain computers

    Quote Originally Posted by gcerulli View Post
    I've run it step by step on his machine and I think I've identified where it fails.
    Care to share what line it errors on. Might be a clue.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    05-11-2016
    Location
    Dallas, TX
    MS-Off Ver
    excel 2016
    Posts
    18

    Re: Object doesn't support...on certain computers

    I should have been clearer. Sorry.

    It's denoted in the code itself:

    'the next line is where it fails

  4. #4
    Forum Contributor frabulator's Avatar
    Join Date
    09-16-2015
    Location
    United States
    MS-Off Ver
    2019
    Posts
    101

    Re: Object doesn't support...on certain computers

    What version of Excel is on your bosses computer?
    *EDIT*
    Sorry, 2013, got it (glanced over that)

  5. #5
    Forum Contributor frabulator's Avatar
    Join Date
    09-16-2015
    Location
    United States
    MS-Off Ver
    2019
    Posts
    101

    Re: Object doesn't support...on certain computers

    Your problem is in Excels older VBA structure. Over the years Microsoft has changed a few things. 'Add2' was one of them. In older versions it needs to be just 'Add', while in newer versions it needs to be 'Add2'. Please see below. I have modified your code a little and added an if statement to check for version differences.


    
    Columns("A:I").Select
        jC.Sort.SortFields.Clear
    
        'Add2 is only used for 2013+
        'for anything 2013 and backwards you need to use Add
        '
        'the if statement below will check for 2010 models and 2013 models and change Add2 to Add
    
    
        If Application.Version = "14.0" Or Application.Version = "15.0" Then
        
            jC.Sort.SortFields.Add Key:=Range("E2:E1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            jC.Sort.SortFields.Add Key:=Range("A2:A1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            jC.Sort.SortFields.Add Key:=Range("F2:F1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        Else
        
            jC.Sort.SortFields.Add2 Key:=Range("E2:E1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            jC.Sort.SortFields.Add2 Key:=Range("A2:A1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            jC.Sort.SortFields.Add2 Key:=Range("F2:F1200"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        End If

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Object doesn't support...on certain computers

    This should be compatible with all Excel 2003 and later versions

    Sub Sort_JobCosting()
        Dim wB        As Workbook
        Dim pE        As Worksheet
        Dim cC        As Worksheet
        Dim cA        As Worksheet
        Dim cM        As Worksheet
        Dim rO        As Worksheet
        Dim jC        As Worksheet
        Dim x         As Integer
        Dim y         As Integer
        Dim z         As Double
        Dim userResponse As String
        Set wB = ThisWorkbook
        Set pE = wB.Worksheets("Paste Expense")
        Set cC = wB.Worksheets("Control Center")
        Set cA = wB.Worksheets("Calculator")
        Set cM = wB.Worksheets("CURRENT MONTH")
        Set rO = wB.Worksheets("Roster")
        Set jC = wB.Worksheets("Job Costing")
        x = 4
        y = 2
        z = 0
        jC.Select
        
        jC.Columns("A:I").Sort Key1:=jC.Range("E1"), Order1:=xlAscending, _
                               Key2:=jC.Range("A1"), Order2:=xlAscending, _
                               Key3:=jC.Range("F1"), Order3:=xlAscending, _
                               Header:=xlYes, MatchCase:=False, _
                               Orientation:=xlTopToBottom, _
                               SortMethod:=xlPinYin
        
        Do Until jC.Cells(y, 1) = ""
    jmp:
            If jC.Cells(y, 1) = jC.Cells(y + 1, 1) And jC.Cells(y, 5) = jC.Cells(y + 1, 5) And jC.Cells(y, 6) = jC.Cells(y + 1, 6) Then
                z = z + jC.Cells(y, 4).Value
                y = y + 1
                GoTo jmp
            Else
                z = z + jC.Cells(y, 4).Value
                Rows(y + 1).Insert shift:=xlDown, _
                                   CopyOrigin:=xlFormatFromLeftOrAbove
                jC.Cells(y + 1, 3) = "Total:"
                jC.Cells(y + 1, 3).HorizontalAlignment = xlRight
                jC.Cells(y + 1, 3).Font.Bold = True
                'If z = 0 Then z = jC.Cells(y, 4).Value2
                jC.Cells(y + 1, 4) = z
                'jC.Cells(y + 1, 4).NumberFormat = "0.00"
                jC.Cells(y + 1, 4).Font.Bold = True
                y = y + 1
            End If
            z = 0
            y = y + 1
        Loop
        
    End Sub

  7. #7
    Registered User
    Join Date
    05-11-2016
    Location
    Dallas, TX
    MS-Off Ver
    excel 2016
    Posts
    18

    Re: Object doesn't support...on certain computers

    These both worked! Thank you so much.

    It also prompted my boss to upgrade to office 2019, so I guess Microsoft wins as well.

    It's a good day when you learn something new.

    This community always bails me out when I am at the end of my rope.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Object doesn't support this property or method - word object
    By Yakov on Excel in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-27-2017, 09:46 AM
  2. Object doesn't support this property or method
    By amartino44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2013, 10:05 AM
  3. Error: Object doesn't support this property
    By Nancy123 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-28-2010, 06:58 AM
  4. Replies: 3
    Last Post: 06-14-2006, 02:10 PM
  5. [SOLVED] object doesn t support this property or metod
    By Ivica Lopar in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-09-2005, 04:35 PM
  6. 438 - Object doesn't support this property or method
    By Revtim in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2005, 05:05 PM
  7. Object doesn't support this method
    By Andibevan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-01-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1