Just some FYI for the OP,
Here is a source of additional information:
http://www.cpearson.com/Excel/Writin...ionsInVBA.aspx
--
Regards,
Tom Ogilvy
"Bernie Deitrick" wrote:
Dylan,
Function CEStatus(myC2 As Range) As String
CEStatus = ""
If myC2.Value = "" _
Or myC2.Value = "Complete" _
Or myC2.Value = "Cancelled" Then _
CEStatus = "Complete"
If myC2.Value = "Forecast" _
Or myC2.Value = "Awaiting Budget Quote" _
Or myC2.Value = "Awaiting Firm Quote" Then _
CEStatus = "Ongoing"
End Function
Used like
=CEStatus(A1)
--
HTH,
Bernie
MS Excel MVP
"Dylan" wrote in message
...
I would like to convert a macro into a UDF to make my sheet automatically
update, and also because I haven't used UDFs and would like to learn about
them.
The macro looks up the value of one cell and depending on the string adds a
text string to the another cell.
Sub Update_CEStatus()
Dim myC2 As Range
Dim WatchRange2 As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set WatchRange2 = Range("Status")
On Error Resume Next
For Each myC2 In WatchRange2
If myC2.Cells.Value = "" _
Or myC2.Cells.Value = "Complete" _
Or myC2.Cells.Value = "Cancelled" Then
myC2.Offset(0, 1).Value = "Complete"
ElseIf myC2.Cells.Value = "Forecast" _
Or myC2.Cells.Value = "Awaiting Budget Quote" _
Or myC2.Cells.Value = "Awaiting Firm Quote" Then
myC2.Offset(0, 1).Value = "Ongoing"
End If
Next myC2
With Application
.ScreenUpdating = False
.Calculation = xlCalculationAutomatic
End With
End Sub
Regards
Dylan