I am getting a runtime 13 error type mismatch when I run this macro. The call out is "if... then".
当我运行这个宏时,我将得到一个运行时13错误类型不匹配。“如果……”然后”。
The code seemingly works correctly. The only issue I can think of is that a seperate macro runs before this one that uses a formula to name cells(1,8) and then a copy, paste values occurs.
代码看起来工作正常。我能想到的唯一问题是,在使用公式命名单元格(1,8)的分片宏之前运行,然后复制粘贴值。
Sub NewWb()
子NewWb()
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Add
For Each Worksheet In wb1.Worksheets
**If Worksheet.Cells(1, 8).Value = "PI Fin Ops" Then**
Worksheet.Move After:=wb2.Sheets(wb2.Sheets.Count)
End If
Next Worksheet
End Sub
终止子
Any help is appreciated!
任何帮助都是赞赏!
1 个解决方案
#1
1
That error would commonly raise if the cell contains an error value. You can easily trap that with the IsError
function.
如果单元格包含一个错误值,这个错误通常会增加。您可以很容易地用IsError函数捕获它。
I would also recommend to avoid using reserved or semi-reserved keywords in your variable naming conventions, such as Worksheet
.
我还建议避免在变量命名约定(如工作表)中使用保留或半保留的关键字。
Dim WS as Worksheet
Dim wb1 as Workbook
Dim wb2 as Workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Add
For Each WS In wb1.Worksheets
If Not IsError(WS.Cells(1,8).Value Then
If WS.Cells(1, 8).Value = "PI Fin Ops" Then
WS.Move After:=wb2.Sheets(wb2.Sheets.Count)
End If
End If
Next WS
#1
1
That error would commonly raise if the cell contains an error value. You can easily trap that with the IsError
function.
如果单元格包含一个错误值,这个错误通常会增加。您可以很容易地用IsError函数捕获它。
I would also recommend to avoid using reserved or semi-reserved keywords in your variable naming conventions, such as Worksheet
.
我还建议避免在变量命名约定(如工作表)中使用保留或半保留的关键字。
Dim WS as Worksheet
Dim wb1 as Workbook
Dim wb2 as Workbook
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Add
For Each WS In wb1.Worksheets
If Not IsError(WS.Cells(1,8).Value Then
If WS.Cells(1, 8).Value = "PI Fin Ops" Then
WS.Move After:=wb2.Sheets(wb2.Sheets.Count)
End If
End If
Next WS