VBA将单元/单元范围传递给多个子单元

Question

嗨，我有一个问题，把一个单元格范围定义为一个变量，取决于哪一组单元格已经改变了。到目前为止，我已经完成了这个任务，但是它会发送多个错误，我尝试将它们作为字符串传递，并创建临时变量来保存值并传递它，但不管它看起来不起作用。

    Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets("Sheet1").Range("A:E"), Target) Is Nothing) Then
    DoSort("A3:F100", "A4")
End If
If Not (Application.Intersect(Worksheets("Sheet1").Range("H:L"), Target) Is Nothing) Then
    DoSort("H3:M100", "H4)
End If
End Sub

Sub DoSort(x As Range, y As Range)
With ThisWorkbook.Sheets("Sheet1")
.Range(x).Sort Key1:=.Range(y), Order1:=xlAscending, Header:=xlYes
End With
End Sub

当我像这样对细胞进行硬编码时，我让它起作用了：

Private Sub DoSort2()
With ThisWorkbook.Sheets("Sheet1")
.Range("H3:M100").Sort Key1:=.Range("H4"), Order1:=xlAscending, Header:=xlYes
End With
End Sub

从来没有真正在VBA与excel宏工作，所以这是非常新的，所以任何帮助都将不胜感激！

Answer 1

请参阅下面的重构代码。请看我的评论来解释。

Private Sub Worksheet_Change(ByVal Target As Range)

    'I used "Me." in place of "Worksheets("Sheet1")." assuming that the Worksheet_Change event is already on Sheet1    
    If Not Intersect(Me.Range("A:E"), Target) Is Nothing Then
         DoSort "A3:F100", "A4"
    End If

    If Not Intersect(Me.Range("H:L"), Target) Is Nothing Then
        DoSort "H3:M100", "H4"  'you were missing a close " here
    End If

End Sub

'define x and y as String to pass the string address of the range reference
Sub DoSort(x As String, y As String)
    With ThisWorkbook.Sheets("Sheet1")
        .Range(x).Sort Key1:=.Range(y), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

如果你想的话，你也可以通过这个范围。看起来是这样的：

   DoSort Me.Range("A3:F100"), Me.Range("A4")

   Sub DoSort(x as Range, y as Range)
      x.Sort Key1:=y, Order1:=xlAscending, Header:=xlYes
   End Sub

Answer 2

您可以将数据作为字符串传递，而不是以范围传递：

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets("Sheet1").Range("A:E"), Target) Is Nothing) Then
        DoSort("A3:F100", "A4")
    End If
    If Not (Application.Intersect(Worksheets("Sheet1").Range("H:L"), Target) Is Nothing) Then
        DoSort("H3:M100", "H4")
    End If
End Sub

Sub DoSort(x As String, y As String)
    With ThisWorkbook.Sheets("Sheet1")
        .Range(x).Sort Key1:=.Range(y), Order1:=xlAscending, Header:=xlYes
    End With
End Sub