View Full Version : [SOLVED:] Run-time error 438: Object doesn't support this property or method
El_Diabolo
11-30-2013, 07:42 PM
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
11-30-2013, 09:13 PM
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
12-01-2013, 06:37 AM
Thanks (again), Eirik. Works perfectly. I think Vikings are just great.
Best regards.
Newton/sqm
11-21-2016, 04:00 AM
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
11-22-2016, 01:12 AM
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
12-15-2017, 05:02 AM
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
12-15-2017, 06:26 AM
I'm pretty sure "Quit" is not a method for the workbook object. Do you mean "wb.Close"?
ciompino
12-15-2017, 07:09 AM
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
03-26-2018, 11:55 PM
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
03-27-2018, 01:31 AM
Which version of Excel do you have?
Tiger129
03-27-2018, 05:19 AM
Which version of Excel do you have?
Im using Excel 2007.
Aflatoon
03-27-2018, 07:25 AM
Then you can't use DisplayFormat so remove that part and use:
Cells(Num, 3).Interior.Color
instead.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.