Small amendment needed to UDF

[Ironman]

Hi

I was kindly given the below code by a member of the board some time ago.

With Worksheets("Daily Tracking")
    uValue = Sheets("Training Log").Range("MlsYTDLessLastYr")
    Select Case uValue
        Case Is < 0
            utext = "less"
        Case Is = 0
            utext = "equal"
        Case Is > 0
            utext = "further"
    End Select
    uValue = Round(uValue, 0)
MsgBox "You have now run " & uValue & " miles " & _
        utext & " than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
End With

Range("MlsYTDLessLastYr") is as below:

Exercise Log.xlsm
	C	D	E
5	311	472	-161
Training Log

Cell Formulas
Range	Formula
C5	C5	=INDEX('Daily Tracking'!375:375,1,DailyTrackingColumn)
D5	D5	=INDEX('Daily Tracking'!372:372,1,DailyTrackingColumn-1)
E5	E5	=C5-D5

The code functions perfectly but it isn't grammatically correct when last year's mileage exceeds this year's mileage, as the message box shows a "-" value e.g. "You have now run -161 miles less than this time last year". This is because E5 has the custom format +0;-0;0 and I would like to keep it that way for clarity.

I would be grateful for an extra row of code that removes the negative symbol from the msgbox statement if the value in cell E5 is negative.

Thank you!

 

[DanteAmor]

Change for this:

MsgBox "You have now run " & Abs(uValue) & " miles " & _
        utext & " than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"

 

[RoryA]

FYI, "You have now run 0 miles equal than this time last year " isn't great grammar either...

 

[Ironman]

Hey DanteAmor that worked perfectly - thank you so much!

@RoryA - Yes, I appreciate that and a solution to that would also be appreciated

 

[Ironman]

I have tried to remedy the above 0 value anomaly by inputting a value of 161 using the below

With Worksheets("Daily Tracking")
    uValue = Sheets("Training Log").Range("MlsYTDLessLastYr")
    Select Case uValue
        Case Is < 1
            utext = "less than"
        Case Is = 0
            utext = "the same number of miles as"
        Case Is > 1
            utext = "further than"
    End Select
    uValue = Round(uValue, 0)
MsgBox "You have now run " & Abs(uValue) & " miles " & _
        utext & " this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
End With

but it no matter what decimal I input, it won't return "the same number of miles as" - it either says "0 miles less than" or "0 miles further than"

 

[DanteAmor]

Try this

Sub test()
  Dim uValue As Double, utext As String
  uValue = Sheets("Training Log").Range("MlsYTDLessLastYr").Value
  Select Case uValue
    Case Is < 0
      utext = "less than"
    Case Is = 0
      utext = "the same number of miles as"
    Case Is > 0
      utext = "further than"
  End Select
  MsgBox "You have now run " & Abs(uValue) & " miles " & _
    utext & " this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
End Sub

 

[Ironman]

Try this

Sub test()
  Dim uValue As Double, utext As String
  uValue = Sheets("Training Log").Range("MlsYTDLessLastYr").Value
  Select Case uValue
    Case Is < 0
      utext = "less than"
    Case Is = 0
      utext = "the same number of miles as"
    Case Is > 0
      utext = "further than"
  End Select
  MsgBox "You have now run " & Abs(uValue) & " miles " & _
    utext & " this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
End Sub

Many thanks again Dante - however, when I input a test amount of 170.85 (equal to last year) the msgbox says "You have now run 0 miles the same number of miles as last year"

 

[Fluff]

How about

    Select Case uValue
        Case Is < 0
            utext = Abs(Round(uValue, 0)) & " miles less than"
        Case Is = 0
            utext = "the same number of miles as"
        Case Is > 1
            utext = Abs(Round(uValue, 0)) & " miles further than"
    End Select
MsgBox "You have now run " & utext & " this time last year ", vbInformation, "Mileage Compared To This Time Last Year"

 

[Ironman]

Many thanks for your help Fluff, but it now returns "you have now run the same number of miles as this time last year" whatever value I input

 

[DanteAmor]

"You have now run 0 miles the same number of miles as last year"

Are you running my macro?
How do you have the uValue variable declared?
What is the cell with this name: "MlsYTDLessLastYr"?
What format do you have in that cell?