Trying to make this work..

In col E i have the folder name.. in Col D i want the Hyperlink.

so i am using the formula =HYPERLINK(thePath(D5,-1))

this code should also put a TRUE or FALSE in col F if the folder exists... but nope lol
Code:
Public Function thePath(pth As Range, Optional offset As Integer = 0) As String
Dim YR As String
Dim MMYYY As String
Dim dPath As String
Dim DT As Date

DT = DateAdd("m", offset, Date)

MMYYYY = Format(DT, "MM-YYYY")
YR = Year(DT)

thePath = "\\xxxxxxxNetworkPathxxxx\ntusers\Line Cost\Reporting\" & YR & " Close Reports\" & MMYYYY & "\" & pth.Value


'Exit Function  <<< NOTE THIS


If Len(Dir(thePath, vbDirectory)) > 0 Then
    ActiveSheet.Cells(pth.Row, pth.Column + 1).Value = "TRUE"
Else
    ActiveSheet.Cells(pth.Row, pth.Column + 1).Value = "FALSE"
End If
End Function
as is, i get the TRUE, but the hyperlink cell says #VALUE!
if I uncomment the Exit, then the path show correctly, but obviously the True False doesnt work.

any ideas??


FYI: I also tried using another function to check the Dir, =PathCheck(D5)

Code:
Public Function PathCheck(sPath As Range) As String

Dim pLen As Long
Dim pStr As String

pLen = Len(Dir(sPath.Text, vbDirectory))

If pLen > 0 Then
    PathCheck = "TRUE"
Else
    PathCheck = "FALSE"
    
End If

End Function
returns false!?? lol even though it does exist?! lol