Have VBA to link to cell on previous sheet, need to modify to link to last cell in column, eliminate seperate cell

[Kusaywa]

I have a cell on Sheet1 which contains the formula =LOOKUP(2,1/(F:F<>""),F:F)
This gives me the last entry in column F
On Sheet2 and so on, I have this code in a module

Function PrevSheet(RCell As Range)
    Dim xIndex As Long
    Application.Volatile
    xIndex = RCell.Worksheet.Index
    If xIndex > 1 Then _
        PrevSheet = Worksheets(xIndex - 1).Range(RCell.Address)
End Function

Then have this formula in a cell =PrevSheet(I3)
What I'm trying do is eliminate the separate cell on the previous sheet that contains =LOOKUP(2,1/(F:F<>""),F:F) and somehow just pull the last entry in column F

Thanks for any help

 

[DanteAmor]

Put the following code in a module

Function LastValue(col As String)
    If ActiveSheet.Index > 1 Then
        Dim s As Worksheet
        Set s = Sheets(ActiveSheet.Index - 1)
        LastValue = s.Range(col & s.Range(col & Rows.Count).End(xlUp).Row).Value
    End If
End Function

In sheet2 in a cell put de formula:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]=LastValue("F")[/TD]
[/TR]
</tbody>[/TABLE]

The "F" is the column from which you want to get the last value.
Try an tell me.

 

[Kusaywa]

It worked if I changed it to a cell that didn't have a formula. Column F contains a formula.

 

[DanteAmor]

Use the following

Function LastValue(col As String)
    If ActiveSheet.Index > 1 Then
        Dim s As Worksheet
        Dim b As Object
        Set s = Sheets(ActiveSheet.Index - 1)
        Set b = s.Columns(col).Find("*", lookat:=xlWhole, LookIn:=xlValues, SearchDirection:=xlPrevious)
        If Not b Is Nothing Then
            LastValue = s.Range(col & b.Row).Value
        End If
    End If
End Function

 

[Kusaywa]

It works when first entering =LastValue("F") but when I flip between sheets, it changes to #VALUE !
Same if I place it in a cell that's not a formula

 

[DanteAmor]

That's right, I also have problems with the function, I review it and comment on it.

 

[Kusaywa]

Thanks for your help.