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