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

-------------------


--
Message posted from http://www.ExcelForum.com

use
..Value


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"scain2004 " wrote in message
...
I'm trying to pull at text value out of a cell has that a formula. How
do I reference the text in the cell, not the formula and return it
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

--------------------



---
Message posted from http://www.ExcelForum.com/

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


--
Message posted from http://www.ExcelForum.com

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


-------------------


--
Message posted from http://www.ExcelForum.com