Custom Function To Read Data

[mouse88]

I have the following function:

Public Function FINDDATA(SearchString As String, FilePath As String, SearchRange As String, ColOffset As Integer, _
RowOffset As Integer) As Variant
Dim WkBook As Workbook
Dim ResultRange As Range
Set WkBook = Workbooks.Open(FilePath, True, True)
Set ResultRange = WkBook.Worksheets("Sheet1").Range(SearchRange).Find(SearchString)
If ResultRange Is Nothing Then
    FINDDATA = "#REF!"
Else
    Set FINDDATA = WkBook.Worksheets("Sheet1").Range.Find(SearchString).Offset(ColOffset, RowOffset).Value
End If
End Function

This stops on the line:
Set ResultRange = WkBook.Worksheets("Sheet1").Range(SearchRange).Find(SearchString)

It doesn't throw any errors just seems to exit the code and gives a #VALUE error.

Can anyone see anything obvious?

Thanks

Matt

 

[mouse88]

Anyone?

 

[mouse88]

I have tried this:

Public Sub FINDDATASUB()
Dim WkBook As Workbook
Dim ResultRange As Range
Set WkBook = Workbooks.Open("C:\Users\Matt\Desktop\01.06.11\Summary.xlsx")
Set ResultRange = WkBook.Worksheets("Sheet1").Range("A:A").Find("Test")
If ResultRange Is Nothing Then
 
Else
    MsgBox (WkBook.Worksheets("Sheet1").Range("A:A").Find("Test").Offset(0, 1).Value)
End If
End Sub

This works fine and it opens the workbook. The value in the cell for my firect function is exactly the same filepath so I cant see any reason why it wont open the workbook.

Heres the formula I was using with the first function:

=FINDDATA("Test", D3,"A:A", 0, 1)