[RESOLVED] Excel HYPERLINK formula using function

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

i tested your second function, worked fine for me

Quote:

---

this code should also put a TRUE or FALSE in col F if the folder exists

---

no, you can not do this with a worksheet function, give them their own formula

when i tested the code it was failing on

Quote:

---

If Len(Dir(thepath, vbDirectory)) > 0 Then

---

without error

ok.. well im stumped. It works today (using the PathCheck function)

?????

I guess... thanks? lol