gpt4 book ai didi

Excel VBA 运行时错误 '438' : Object doesn't support this property or method

转载 作者:行者123 更新时间:2023-12-04 19:58:43 27 4
gpt4 key购买 nike

我在 VBA 中运行这段代码,但我不知道我的错误在哪里。我不断收到错误消息

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



我在互联网上搜索了解决方案,但找不到任何解决我问题的方法。
 Sub FinalCleanUp()
Dim wkbk As Workbook
Dim wksht As Worksheet
Dim DataSheet As Worksheet
Dim sheetName As String

sheetName = "Data"
Set wkbk = ActiveWorkbook

'Delete consolidated data sheet if it already exists
For Each wksht In wkbk.Sheets
If wksht.Name = sheetName Then
wkbk.Sheets(sheetName).Delete
Exit For
End If
Next wksht

'Create new sheet for consolidated data
wkbk.Sheets.Add Before:=wkbk.Sheets(1)
Set DataSheet = ActiveSheet
ActiveSheet.Name = sheetName

'Step through each sheet and copy data to consolidated data sheet
'ERROR IS SOMEWHERE BELOW HERE
For Each wksht In wkbk.Sheets
If wksht.Name <> sheetName Then
wksht.Activate
Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count)).Copy
If wksht = wkbk.Sheets(2) Then
DataSheet.Activate
Cells(ActiveSheet.UsedRange.Rows.Count, 1).Select
Else
DataSheet.Activate
Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).Select
End If
ActiveSheet.Paste
End If
Next wksht

End Sub

此代码应该从多个工作表中获取数据并将其合并到一个工作表中。如前所述,运行宏时出现运行时错误并且没有输出。

最佳答案

错误在这里:

If wksht = wkbk.Sheets(2) Then
Worksheet类没有默认属性,因此您不能强制强制该对象; VBA 正在尝试调用 Worksheet 的默认成员满足比较运算符,但没有这样的成员,因此引发错误 438。

我认为您要做的是验证是否 wkshtwkbk.Sheets(2) .您需要 Is引用相等的运算符:
If wksht Is wkbk.Sheets(2) Then

请注意,这与比较 .Name 不同。工作表:这里我们比较对象引用。

也就是说,我会在这里买一两个元音,打错字太容易了……确保 Option Explicit已指定!

附录

wkbk.Sheets.Add Before:=wkbk.Sheets(1)
Set DataSheet = ActiveSheet

Worksheets.Add返回添加的工作表对象,因此您可以这样做,而不是依赖添加的工作表的副作用现在是 ActiveSheet :
Set DataSheet = wkbk.Sheets.Add(Before:=wkbk.Sheets(1))

关于Excel VBA 运行时错误 '438' : Object doesn't support this property or method,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56706603/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com