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!

 

[Ironman]

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?

Hi Dante

Below is the code of yours that I used, with the result I stated, before I received the others' alternative solutions:

With Worksheets("Daily Tracking")
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 With

I'm sorry, I don't understand "How do you have the uValue variable declared?"

Cell E5 is "MlsYTDLessLastYr"

That cell is in the custom format +0;-0;0 (the +/- values are intentional, to make it clearer)

Thanks again Dante

 

[DanteAmor]

That's not my code from post #6.
You could put your complete code here.

 

[Ironman]

Hi Dante - I don't mean to be impolite but it was your code. I had to omit your first and last rows (Sub test() and End Sub) because the code is part of a larger sub, which isn't relevant to this.

The existing opening row is

With Worksheets("Daily Tracking")

And the end row is

End With

Here it is again, without the first and last row, just as it is in my worksheet.

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"

Thanks for your patience

 

[DanteAmor]

But that's not the complete code, lines are missing, for example:

Function namefunction()
Dim uValue As Double, utext As String


End Function

or

Sub test()
  Dim uValue As Double, utext As String

End sub

etc.

Please, You could put your complete code here.

 

[Ironman]

Hi Dante, this is what you asked for:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("EntRng")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Worksheets("Training Log")
  MsgBox "Lifetime Mileage: " & Format$(.Range("F5").Value, "#,##0") & "   " & vbNewLine & vbNewLine & _
         "Year to Date Mileage: " & Format$(.Range("C5").Value, "#,##0") & "   ", vbInformation, "Mileage Totals      "
End With

With Worksheets("Daily Tracking")
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 With

If Range("CurYTD").Value > Range("CurGoal").Value Then
MsgBox ("Congratulations!" & vbNewLine & "You have now run more miles this year than" & vbNewLine & vbNewLine & _
   "- The whole of " & Range("PreYear").Value & vbNewLine & _
   "- " & Range("counter").Value & " of the " & Year(Now) - 1981 & " years you've been running" & vbNewLine & _
   vbNewLine & "New rank for " & Year(Now) & " is " & Range("CurYTD").Offset(1, 0).Value & " out of " & Year(Now) - 1981), _
   vbInformation, "Another Year End Mileage Total Exceeded!     "
   
Range("counter").Value = Range("Counter").Value + 1
Else
MsgBox (CLng(Range("CurGoal").Value - Range("CurYTD").Value) & _
   " miles to go until rank " & (Range("CurYTD").Offset(1, 0).Value) - 1 & " reached" & vbNewLine & vbNewLine & _
   "(Year end mileage for " & Range("PreYear").Value & ")"), _
vbInformation, "Year To Date Mileage"
End If
Application.EnableEvents = True
End If
End Sub

 

[DanteAmor]

Sorry, I am not understanding what the problem is.
This already worked, you just had to change the sign, but now it seems that nothing works.
You can share your file in the cloud like google drive or dropbox.

 

[Ironman]

Case Is < 0 works OK. Thank you again.

Case Is > 0 also works OK. Thank you again.

Case Is = 0 does not work correctly.

 

[DanteAmor]

Case Is = 0 does not work correctly.

What this message means:

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"

170.85 (equal to last year), If they are equal, then the result of C5-D5 = 0, then the message should read 0. I am confused ?

 

[Ironman]

The statement is mathematically correct. However, grammatically, it makes no sense. All I am asking is the message to read correctly.

"You have now run the same number of miles as last year" would be perfect

 

[Ironman]

I guess this is the best I can expect to get

Case Is = 0
            utext = "less than"

Unless the code can be rewritten slightly so it says "You have now run the same number of miles as last year"