Convert Macro into UDF

D

Dylan

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
 
B

Bernie Deitrick

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)
 
D

Dylan

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

I need to consider using these in future.

Regards
Dylan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top