Consulting

Results 1 to 12 of 12

Thread: Run-time error 438: Object doesn't support this property or method

  1. #1

    Run-time error 438: Object doesn't support this property or method

    Hi All,

    I have run into the above error while trying to use variables for workbook and worksheet names. The workbook in question is the active workbook and the sheet exists. I have tried searching for the solution, but I have not found it and I can't figure it out. There may be other ways to do it, but I would like to know what is wrong with this code please.

    Dim wkbDest As Workbook
    Dim TP As Worksheet
    Set wkbDest = Workbooks("Table Plan for Cameron.xlsm")
    Set TP = Worksheets("TablePlan")
    wkbDest.TP.Range("A1:H24").Clear      ' This is where the error 438 happens
    Many thanks.

  2. #2
    Would using "wkbDest.TP.Range("A1:H24").ClearContents" do the trick?

    -edit- Nevermind, the problem isn't the function called, it's with how you define your objects. You need to do it like this:

    Option Explicit
    
    Sub test()
      Dim wkbDest As Workbook
      Dim TP As Worksheet
      
      Set wkbDest = Workbooks("Table Plan for Cameron.xlsm")
      Set TP = wkbDest.Worksheets("TablePlan")
      
      TP.Range("A1:H24").Clear ' This is where the error 438 happens
      
    End Sub

  3. #3
    Thanks (again), Eirik. Works perfectly. I think Vikings are just great.

    Best regards.

  4. #4
    I have a similar problem and thought I might just post it here and not adding another thread. I also get that error ot the code:

    Option ExplicitPrivate Sub CommandButton4_Click()
    
    
    
    
    Dim Obj As Range
    
    
    
    
    Set Obj = Worksheets("Tests Results").ListObjects("Test_results").Find(SampleID.Value)  'Error appears here
    
    
     
    With Obj
        .ListColumns("Test Lab") = Me.ComboBox_LAB.Value
        .ListColumns("Flammability Type ") = Me.ComboBoxFlamm.Value
        .ListColumns("Avg-Smoke Density Pass Value (Ds)") = Me.ComboBoxSDpass.Value
    end With
    End Sub
    Here I have a userform and the user enters a SampleID and some data (See With). The code should search for the SampleID in and edit the tablerow. Any help is appreciated.

  5. #5
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    Not far
    Posts
    1,541
    The error is because a ListObject doesn't have a Find method. That belongs to a Range object - so you need:

    Set Obj = Worksheets("Tests Results").ListObjects("Test_results").DataBodyRange.Find(SampleID.Value)
    Be as you wish to seem

  6. #6
    VBAX Newbie
    Joined
    Dec 2017
    Posts
    2
    Location
    Hi all, I am new in this forum.

    I have the same problem with runtime error 438.

    Last week every things worked good, instead this week has apprared the error's popup.

    Also, I have another routine that works well with the same specific....I don't know why

    However I am posting code to ask you a solution

    Sub ProcessFiles()    Dim Filename, Pathname As String
        Dim wb As Workbook
    
    
        Pathname = "C:\Users\tecnico2\Desktop\revisione prova\"
        Filename = Dir(Pathname & "*.xlsm")
          
        Do While Filename <> ""
            Set wb = Workbooks.Open(Pathname & Filename)
            DoWork wb
                    
            wb.Save
            wb.Quit    '<-------------- the error appears here-----------
          
            Filename = Dir()
        Loop
    End Sub
    
    
    
    
    Sub DoWork(wb As Workbook)
        With wb
            Call AddDBFasi
        End With
    End Sub

  7. #7
    I'm pretty sure "Quit" is not a method for the workbook object. Do you mean "wb.Close"?

  8. #8
    VBAX Newbie
    Joined
    Dec 2017
    Posts
    2
    Location
    Thank you very much. Now every things work great!
    Quote Originally Posted by EirikDaude View Post
    I'm pretty sure "Quit" is not a method for the workbook object. Do you mean "wb.Close"?

  9. #9
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    2
    Location
    Hi everyone,

    I have the same error . I'm still a newbie with VBA and would appreciate if anyone can help me. Below is the code I'm working at and would be glad if anyone can tell me what's wrong:

    Sub PutConditionalColorsIntoChart2()
    
    
    Dim Num As Integer 
    
    
     ActiveSheet.ChartObjects("Chart 43").Activate
     ActiveChart.ChartArea.Select
     
    For Num = 3 To 16 
        ActiveChart.SeriesCollection(1).Points(Num - 2).Interior.Color = Cells(Num, 3).DisplayFormat.Interior.Color
    
    
    Next Num

  10. #10
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    Not far
    Posts
    1,541
    Which version of Excel do you have?
    Be as you wish to seem

  11. #11
    VBAX Newbie
    Joined
    Mar 2018
    Posts
    2
    Location
    Quote Originally Posted by Aflatoon View Post
    Which version of Excel do you have?
    Im using Excel 2007.

  12. #12
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    Not far
    Posts
    1,541
    Then you can't use DisplayFormat so remove that part and use:

    Cells(Num, 3).Interior.Color
    instead.
    Be as you wish to seem

Tags for this Thread

Posting Permissions

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