Excel 2000 Returning text value Not formula from Cell

[scain2004]

I'm trying to pull at text value out of a cell has that a formula. Ho
do I reference the text in the cell, not the formula and return i
through the function name.

This is what I have:


Code
-------------------

Public Function FindJobDates(strJobName As String, ByVal wbkBook As String, wrkSht As String) As String
Dim rngJob As Range
Dim i As Integer

On Error Resume Next

Set rngJob = Workbooks(wbkBook).Worksheets(wrkSht) _
.Range("Calendar").Cells.Find(strJobName, LookIn:=xlValues)
If Not rngJob Is Nothing Then
For i = 0 To 20
With Workbooks(wbkBook).Worksheets(wrkSht)
MsgBox .Cells(rngJob.Row - i).Text
If IsDate(.Cells(rngJob.Row - i, rngJob.Column).Text) Then
FindJobDates = .Cells(rngJob.Row - i).Text
Exit For
End If
End With
Next
Else
MsgBox "Job Not Found In Calendar."
End If
End Function

 

[Patrick Molloy]

use
..Value

 

[scain2004]

Tried that, came up ""....same as it's doing now.

 

[scain2004]

Ok, figured it out myself :


Code
-------------------

Public Function FindJobDates(strJobName As String, ByVal wbkBook As String, wrkSht As String) As String
Dim rngJob As Range
Dim i As Integer
Static rngJobNext As Range

On Error Resume Next
Set rngJob = Workbooks(wbkBook).Worksheets(wrkSht) _
.Range("A:G").Cells.Find(strJobName, LookIn:=xlValues)

If Not rngJob Is Nothing Then
For i = 0 To 20
With Workbooks(wbkBook).Worksheets(wrkSht)
'MsgBox .Cells(rngJob.Row - i, rngJob.Column).Value
If IsDate(.Cells(rngJob.Row - i, rngJob.Column).Value) Then
FindJobDates = .Cells(rngJob.Row - i, rngJob.Column).Value
Exit For
End If
End With
Next
Else
MsgBox "Job Not Found In Calendar."
End If
End Function