使用 Target.Address 引用范围

Question

我正在尝试在选择单元格时使用MsgBox。我需要根据选择的单元格出现不同的消息框，所以我正在尝试使用和IF语句。出了点问题，我不知道是什么。这是代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rngPH1, rngPH2, rngPH3, rngPH4, rngPH5, rngPH6, rngPH7, rngPH8 As Range
Set ws1 = Worksheets("Budget Hours")
Set ws2 = Worksheets("Schedule")
Set rngPH1 = ws2.Range("E7:E27")
Set rngPH2 = ws2.Range("E43:E63")
Set rngPH3 = ws2.Range("E79:E99")
Set rngPH4 = ws2.Range("E115:E135")
Set rngPH5 = ws2.Range("E151:E171")
Set rngPH6 = ws2.Range("E187:E207")
Set rngPH7 = ws2.Range("E222:E242")
Set rngPH8 = ws2.Range("E259:E279")
If Target.Address = rngPH1 Then
Dim rng1 As Range, rng2 As Range, msg1, i1 As Long

Set rng1 = ws1.Range("E6:E10")
Set rng2 = rng1.Offset(0, Target.Row - 6)
msg1 = rng2.EntireColumn.Cells(3).Value & vbNewLine
For i1 = 1 To rng1.Cells.Count
msg1 = msg1 & vbNewLine & rng1.Cells(i).Value & " - " & rng2.Cells(i).Value & " Hours"
Next i1
MsgBox msg1, , ws1.Range("E5").Value
ElseIf Target.Address = rngPH2 Then
Dim rng3 As Range, rng4 As Range, msg2, i2 As Long

Set rng3 = ws1.Range("E15:E19")
Set rng4 = rng3.Offset(0, Target.Row - 42)
msg2 = rng4.EntireColumn.Cells(3).Value & vbNewLine
For i2 = 1 To rng3.Cells.Count
msg2 = msg2 & vbNewLine & rng3.Cells(i).Value & " - " & rng4.Cells(i).Value & " Hours"
Next i2
MsgBox msg2, , ws1.Range("E14").Value
End If
End Sub

我在第一个IF语句中收到"类型不匹配"错误。为什么？我能做些什么来解决这个问题？

Answer 1

If Target.Address = rngPH1

类型不匹配是因为左侧是String，右侧等效于rngPH1.Value，这是一个 2DVariant数组。

通常的方法是使用Intersect：

If Not Intersect(Target, rngPH1) Is Nothing Then

Answer 2

@BigBen提供了您问题的答案。

但是，您的代码充满了您应该考虑使用的模式。在代码流中使用可用的模式将大大减少代码行，并帮助您使代码更加动态和易于管理。我怀疑在不久的将来，您将继续在rngPH8rngPH3中添加"if"语句。我在您的代码中使用了模式并将其简化为以下内容：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rngPH As Range, rng1 As Range, rng2 As Range
Dim msg As String, i As Long, j As Long

Set ws1 = Worksheets("Budget Hours")
Set ws2 = Worksheets("Schedule")

For i = 1 To 8

Set rngPH = ws2.Range("E" & 7 + (i - 1) * 36).Resize(21, 1)
If Not Intersect(rngPH, Target) Is Nothing Then

Set rng1 = rngPH.Cells(1, 1).Offset(-1, 0).Resize(5, 1)
Set rng2 = rng1.Offset(0, Target.Row - rng1.Cells(1, 1).Row)

msg = rng2.EntireColumn.Cells(3).Value & vbNewLine

For j = 1 To rng1.Cells.Count
msg = msg & vbNewLine & rng1.Cells(j).Value & " - " & rng2.Cells(j).Value & " Hours"
Next j
MsgBox msg, , rng1.Cells(1, 1).Offset(-1, 0).Value
Exit For
End If

Next i

End Sub

重要提示：

仅当

• 且仅当原始代码中的rngPH7实际上应设置为ws2.Range("E223:E243")时，此逻辑才有效。如果没有，您可以简单地在上面的某处添加一个空行，使其遵循模式。

• 此代码尚未经过测试，可能存在或不存在的错误和错误。我在这里的唯一目的是向您展示，如果您在开始时多花几分钟(在某些情况下甚至数小时)来分析您的问题并找出任何模式，从长远来看，您将节省的精力和时间。

万事如意