Make UDF function flexible to any sheet

[YasserKhalil]

Hello everybody
I have devised this udf function that brings the address of the last non zero of specific range

Function LastNonZero(Rng As Range) 
    Dim LastRow As Long, lCol As Long, X As Long, I As Long, Cell As Range 
     
    For Each Cell In Rng.Columns 
        If Cells(Rows.Count, Cell.Column).End(xlUp).Row > X Then X = Cells(Rows.Count, Cell.Column).End(xlUp).Row: lCol = Cell.Column 
    Next Cell 
     
    LastRow = Rng.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
    For I = LastRow To 1 Step -1 
        If Cells(I, lCol) > 0 Then Exit For 
    Next I 
     
    LastNonZero = Cells(I, lCol).Address 
End Function

=LastNonZero(A1:B100)

It is working well but for the activesheet only
How it can be fixed to deal with any sheet specified in the parameters ..or I imagine

=LastNonZero(Sheet1!A1:B100)

If Sheet1! existed so the function would deal with Sheet1 and if missing to deal with the active sheet
I tried to fix it but I failed

Thanks advanced for any help

[berlan]

try (untested)

Function LastNonZero(Rng As Range)

    Dim LastRow As Long, lCol As Long, X As Long, I As Long, Cell As Range

    For Each Cell In Rng.Columns
        If Rng.Parent.Cells(Rows.Count, Cell.Column).End(xlUp).Row > X Then X = Rng.parent.Cells(Rows.Count, Cell.Column).End(xlUp).Row: lCol = Cell.Column
    Next Cell

    LastRow = Rng.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For I = LastRow To 1 Step -1
        If Rng.Parent.Cells(I, lCol) > 0 Then Exit For
    Next I

    LastNonZero = Rng.Parent.Cells(I, lCol).Address
    
End Function

If you only write Cells( ...) it will give you the references on the activesheet. rng.parent = the sheet from the range, so I added that. See how it works, no other changes..

[YasserKhalil]

Mr. Berlan
I struggled for two hours trying to fix it and you fixed it in a minute with just one magic word "parent"
Thank you very much for this great really great help
Regards

[berlan]

You're welcome buddy, and thanks for the kind feedback and rep