vba How to pass Range Variables between function and Sub?

[Peter h]

I'm trying to learn how to do this, and no matter how much I read, I'm having a hard time grasping exactly how to do this. So here is the code I'm writing, but for some reason can't seem to get it to work. I'm trying to set "tRNG" in a function, and then pass that variable to my sub. Simple enough, but I'm having a hard time with this.

Sub Clear_Data()
Dim ws As Worksheet
Dim tNum As Range   'Tray Selection Cell
Set ws = ThisWorkbook.ActiveSheet
Set tNum = ws.Cells(2, 1)
If tNum = "" Then
    MsgBox "Please select a tray in cell ""A2"".", vbOKOnly
    Exit Sub
End If
If MsgBox("You are about to clear the data for " & tNum.Text & "." & vbNewLine & vbNewLine _
    & "Is this correct?", vbYesNo) = vbNo Then Exit Sub
    
[COLOR=#FF0000]ws.Range(trayRNG(tRNG)).ClearContents[/COLOR]   'THIS IS JUST A GUESS
End Sub

Private Function trayRNG(ByRef tRNG As Range)
Dim ws As Worksheet
Dim tNum As Range   'Tray Selection Cell
Dim tRNG As Range   'Range containing data to be cleared
Set ws = ThisWorkbook.ActiveSheet
Set tNum = ws.Cells(2, 1)
Select Case tNum.Value
    Case Is = "Tray 1"
        Set tRNG = ws.Range("G5:H7,D8:H19,D21:H34,G20:H20,J8:K34")
    Case Is = "Tray 2"
        Set tRNG = ws.Range("P5:Q7,M8:Q19,M21:Q34,P20:Q20,S8:T34")
    Case Is = "Tray 3"
        Set tRNG = ws.Range("Y5:Z7,V8:Z19,V21:Z34,Y20:Z20,AB8:AC34")
    Case Is = "Tray 4"
        Set tRNG = ws.Range("AH5:AI7,AE8:AI19,AE21:AI34,AH20:AI20,AK8:AL34")
End Select
End Function

Thank You for any help with this.

 

[Peter h]

I got this worked out.

 

[XCitableBill]

Peter - what was your solution? I'm trying to do the same thing but not with a range variable. I'm passing a single argument to a function. That function finds the correct data, but I can't seem to get that data to pass back to the calling sub.
Thanks
Bill

 

[Norie]

Bill

Can you start a new thread and post your code there?

 

[XCitableBill]

Bill

Can you start a new thread and post your code there?

Sure thing.