365 Office 运行时 1004 调试

Question

何时创建表数据 "运行时错误 1004 VBA">

我使用的是 365 office，代码在 excel 2013 中运行良好，现在它在设置范围时在 tableData 行中给了我一个错误

Sub SendEmails(ByVal eMailType As String, ByVal SendEmailTo As String, ByVal SendEmailToCC As String)
Dim ws_email_templates As Worksheet
Dim Email_Send_From, Email_Subject, Email_Body1, Email_Body2 As String
Dim emailTypeRow As Long
Dim tableData As Range
ThisWorkbook.Activate
Set ws_email_templates = ThisWorkbook.Sheets("email_templates")
Set tables = ThisWorkbook.Sheets("tranlsate_Table")
emailTypeRow = ws_email_templates.Range("A1:I1").Find(eMailType).Column

GetEmailBody1 = ws_email_templates.Cells(4, emailTypeRow)
GetEmailBody2 = ws_email_templates.Cells(5, emailTypeRow)
GetEmailPriority = ws_email_templates.Cells(6, emailTypeRow)
Email_Send_From = ws_email_templates.Cells(2, emailTypeRow)
Email_Subject = ws_email_templates.Cells(3, emailTypeRow)
If Right(eMailType, 18) = "contractEnd_noData" Or Right(eMailType, 17) = "creditTime_noData" Then
Email_Body = GetEmailBody1
Else
tabelaDataColumn = tables.Range("A1:Z1").Find(eMailType).Column
tabelaDataLastRow = tables.Cells(1000, tabelaDataColumn).End(xlUp).Row
'Set tableData = tables.Range("V2:Z3")
**'error
Set tableData = tables.Range(Cells(2, tabelaDataColumn), Cells(tabelaDataLastRow, tabelaDataColumn + 4))**
Email_Body = GetEmailBody1 & RangetoHTML(tableData) & GetEmailBody2
End If

Answer 1

Set tableData = tables.Range(Cells(2, tabelaDataColumn), Cells(tabelaDataLastRow, tabelaDataColumn + 4))

该代码将正常工作...只要ThisWorkbook.Sheets("tranlsate_Table")(带有该错别字(是ActiveSheet.

如果tables工作表未处于活动状态，则非限定Cells成员调用将违反Global，并转发到任何ActiveSheet。

既然Range知道它在什么工作表中(通过它的Parent属性(，那么......

Sheet1.Range(Sheet2.Cells(1, 1), Sheet2.Cells(10, 10))

。不是 Excel 可以解决的问题:)

只需使用您要处理的Worksheet对象限定Cells成员调用即可。

Set tableData = tables.Range(tables.Cells(2, tabelaDataColumn), tables.Cells(tabelaDataLastRow, tabelaDataColumn + 4))

请注意，Rubberduck(我管理的开源 VBE 加载项项目(可以帮助您在代码中找到所有此类隐式 ActiveSheet 引用。