VBA: Pass a range from a Worksheet event to another sub

[gailb]

In this Worksheet change event, the user will pick something from B2 and B4. Upon choosing either of those two cells, I would like to pass that range to another sub.

If you look at the Sub, whatever selection is make on the sheet should be pass to the replace and replace the current Range("B2")

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range: Set rng = Target.Parent.Range("B2, B4")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub

    Select Case Target.Address
        Case "$B$2"
            Call Option8(Target.Address)
        Case "$B$4"
            Call Option8(Target.Address)
    End Select

End Sub

Code: Select all

Sub Option8()
    StartRow = Application.Match(wsDest.Range("B2"), wsSrc.Range("A:A"), 0)
End Sub

[HansV]

I'd do it as follows:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    If Target.Count > 1 Then Exit Sub
    Set rng = Range("B2, B4")
    If Intersect(Target, rng) Is Nothing Then Exit Sub

    Call Option8(Target)

End Sub

and

Code: Select all

Sub Option8(r As Range)
    StartRow = Application.Match(r, wsSrc.Range("A:A"), 0)
End Sub

[gailb]

That's exactly what I needed. Thanks.