时间戳

首页 > 解决方案 > 需要类型不匹配错误的解决方案

vba - 需要类型不匹配错误的解决方案

问题描述

我试图通过每个循环遍历给定范围内的所有单元格。

代码运行一次，但之后出现运行时错误 1004、应用程序定义或对象定义错误

以下是我遇到问题的代码块：

Dim cell As Range
Dim wbInstallation As String

wbInstallation = Application.Workbooks(2).Name
'Debug.Print wbInstallation.Name

For Each cell In Workbooks(wbInstallation).Sheets(wsName).Range(Cells(Start_Row_Num, Start_Column_Num), _
                        Cells(Last_Row_Num, Last_Column_Num))
                    If cell.Interior.Color = RGB(204, 0, 0) Then
                        Workbooks(2).Sheets(wsName).Range(Cells(cell.Row, 1), Cells(cell.Row, Last_Column_Num)).Copy
                        ThisWorkbook.Sheets(wsName).Range("A8").PasteSpecial
                        Application.CutCopyMode = False
                    End If
Next

我以不同的方式尝试了它，但它给了我一个类型不匹配错误，运行时错误 13。以下是代码：

Dim cell As Range
Dim wbInstallation As Workbook


Set wbInstallation = Application.Workbooks(2)
'Debug.Print wbInstallation.Name

For Each cell In Workbooks(wbInstallation).Sheets(wsName).Range(Cells(Start_Row_Num, Start_Column_Num), _
                        Cells(Last_Row_Num, Last_Column_Num))
                    If cell.Interior.Color = RGB(204, 0, 0) Then
                        Workbooks(2).Sheets(wsName).Range(Cells(cell.Row, 1), Cells(cell.Row, Last_Column_Num)).Copy
                        ThisWorkbook.Sheets(wsName).Range("A8").PasteSpecial
                        Application.CutCopyMode = False
                    End If
Next

请帮助！

标签： vbaexcel

解决方案

您需要在代码中引用正确的工作表，即在引用单元格时引用工作簿和工作表。见下文：

For Each cell In Workbooks(wbInstallation).Worksheets (wsName).Range(Cells(Start_Row_Num, Start_Column_Num), _
  Cells(Last_Row_Num, Last_Column_Num))

改成：

For Each cell In Workbooks(wbInstallation).Worksheets(wsName).Range( _
  Workbooks(wbInstallation).Worksheets(wsName).Cells(Start_Row_Num, Start_Column_Num), _
  Workbooks(wbInstallation).Sheets(wsName).Cells(Last_Row_Num, Last_Column_Num))

和这个：

Workbooks(2).Sheets(wsName).Range(Cells(cell.Row, 1), Cells(cell.Row, Last_Column_Num)).Copy

对此：

Workbooks(2).Sheets(wsName).Range( _
  Workbooks(2).Worksheets(wsName).Cells(cell.Row, 1), _
  Workbooks(2).Worksheets(wsName).Cells(cell.Row, Last_Column_Num)
).Copy

这看起来很难看，所以我建议您将对特定工作表的引用存储在变量中，例如：

Dim s As Worksheets
Set sheet = 'sheet assignment

推荐阅读