Ironman
Well-known Member
- Joined
- Jan 31, 2004
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
Hi
I was kindly given the below code by a member of the board some time ago.
Range("MlsYTDLessLastYr") is as below:
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!
I was kindly given the below code by a member of the board some time ago.
VBA Code:
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:
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!