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" <(E-Mail Removed)> wrote in message
> > news:8E86DA59-BBA1-43C4-852E-(E-Mail Removed)...
> > >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
> > >
> >
> >
> >
Similar Threads
