vba - 私有(private)子上的excel类型不匹配错误

Question

我有这个子程序，当相应的单元格发生更改时，它应该更新单元格的值，但我不断收到一个错误，提示错误 13 类型不匹配，我查找了此问题的所有可能来源，但无法找出导致它的原因，这是我提出问题的代码:

Private Sub Worksheet_Change(ByVal Target As range)
'Adds unique keyA values
'Check to see if the changed cell is in column H
    If Not Intersect(Target, range("H:H")) Is Nothing Then

        If Target.Cells.Value <> "" And Target.Row > 7 And Target.Row <= 20 Then

'Update the "KeyA" value
            range("A" & Target.Row).Value = Now()

        End If

    End If
'Adds unique keyB values
'Check to see if the changed cell is in column J
    If Not Intersect(Target, range("J:J")) Is Nothing Then

        If Target.Cells.Value <> "" And (Target.Row > "7" And Target.Row <= "27") Then

'Update the "KeyB" value
            range("M" & Target.Row).Value = Now()

        End If

    End If

End Sub

当我尝试清除其检查更改的范围时会发生错误。任何帮助解决这个问题将不胜感激，谢谢!

Answer 1

你忘了Target是一个范围，不一定只包含一个单元格。如果您稍微调整一下代码，它应该可以工作:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

'Adds unique keyA values
'Check to see if the changed cell is in column H
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    For Each cell In Target.Cells
        If cell.Value <> vbNullString And Target.Row > 7 And Target.Row <= 20 Then
            'Update the "KeyA" value
            Range("A" & Target.Row).Value = Now()
        End If
    Next cell
End If

'Adds unique keyB values
'Check to see if the changed cell is in column J
If Not Intersect(Target, Range("J:J")) Is Nothing Then
    For Each cell In Target.Cells
        If cell.Value <> vbNullString And (Target.Row > "7" And Target.Row <= "27") Then
            'Update the "KeyB" value
            Range("M" & Target.Row).Value = Now()
        End If
    Next cell
End If

End Sub

基本上，变化是它现在检查 每个 Target 范围内的单元格并为该范围内的每个单元格工作您的代码。之前，您在比较(可能)多个 Target.Values至 "" .那没有用。现在，只有一个 .Value与 "" 进行比较那应该解决它。