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

[El_Diabolo]

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.

[EirikDaude]

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

[El_Diabolo]

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

Best regards.

[Newton/sqm]

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.

[Aflatoon]

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)

[ciompino]

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


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

[EirikDaude]

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

[ciompino]

Thank you very much. Now every things work great!

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

[Tiger129]

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

[Aflatoon]

Which version of Excel do you have?

[Tiger129]

Which version of Excel do you have?

Im using Excel 2007.

[Aflatoon]

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


Cells(Num, 3).Interior.Color

instead.