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.

Code:

---

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.

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:

Code:

---

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

---

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

Best regards.

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:

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.

The error is because a ListObject doesn't have a Find method. That belongs to a Range object - so you need:

Code:

---

Set Obj = Worksheets("Tests Results").ListObjects("Test_results").DataBodyRange.Find(SampleID.Value)

---

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:dunno

However I am posting code to ask you a solution :)

Code:

---

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

---

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

Thank you very much. Now every things work great!

Quote:

---

Originally Posted by EirikDaude

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

---

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:

Code:

---

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

---

Which version of Excel do you have?

Quote:

---

Originally Posted by Aflatoon

Which version of Excel do you have?

---

Im using Excel 2007.

Then you can't use DisplayFormat so remove that part and use:

Code:

---

Cells(Num, 3).Interior.Color

---

instead.