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

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

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

Thanks guys, this is a lot neater than my previous macro.

I need to consider using these in future.

Regards
Dylan

"Tom Ogilvy" wrote:

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