在范围内的单元格上迭代时，键入不匹配错误 [英] Type mismatch error when iterating over cells in a range

问题描述

我试图在我的电子表格上运行以下vb代码，但是我在Randge中收到错误，因为数据类型不匹配。我只是试图锁定单元格的值为0.有一些单元格#NA值有任何想法？

  Sub Test（）
 Dim Cell As Range 
 Set MyPlage = Range（J6：J1074）
 MyPlage.Cells中的每个单元格
如果不是IsError（单元格）然后
如果范围（J6：J1074）。值=0然后
范围（J6：J1074）。Locked = True 
如果
 
 
结束如果
 
下一个
结束子
  

解决方案

尝试这一个：

  Sub Test（）
 Dim Cell As Range 
 Dim MyPlage As Range 
 
 With ThisWorkbook.ActiveSheet 
 .Unprotect 
 .Cells.Locked = False 
 Set MyPlage = .Range（J6：J1074） 
对于MyPlage中的每个单元格
如果不是IsError（单元格）然后
如果Cell.Value =0则
 Cell.Locked = True 
 End If 
结束如果
 Next 
 .Protect 
 End with 
 
 End Sub 
  

BTW，最好将 ActiveSheet 更改为工作表（SheetName）

Hi I am trying to run the following vb code on my spreadsheet but I am getting an error during the Randge that there is a data type mismatch. I am just trying to lock the cells where the value is 0. There are some cells with #NA values Any ideas?

Sub Test()
Dim Cell As Range
Set MyPlage = Range("J6:J1074")
For Each Cell In MyPlage.Cells
    If Not IsError(Cell) Then
        If Range("J6:J1074").Value = "0" Then
       Range("J6:J1074").Locked = True
        End If


    End If

Next
End Sub

解决方案

Try this one:

Sub Test()
    Dim Cell As Range
    Dim MyPlage As Range

    With ThisWorkbook.ActiveSheet
        .Unprotect
        .Cells.Locked = False
        Set MyPlage = .Range("J6:J1074")
        For Each Cell In MyPlage
            If Not IsError(Cell) Then
                If Cell.Value = "0" Then
                    Cell.Locked = True
                End If
            End If
        Next
        .Protect
    End With

End Sub

BTW, it's better to change ActiveSheet to Worksheets("SheetName")

这篇关于在范围内的单元格上迭代时，键入不匹配错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！