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