Noob error with simple formula within VBA

[Ironman]

Hi

I have the following code:

With Worksheets("Daily Tracking")
MsgBox "You have now run " & MlsYTDLessLastYr & "miles " & _
If(MlsYTDLessLastYr < 0, "less", "more") & _
" than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
End If
End With

What I am looking for is for Excel to look at the named range (which is a single cell with a value, either negative or positive). If it's negative or positive I want to use the word "less" or "more".

Right now, Excel is ignoring the third row completely and the msg is "You have now run miles than this time last year" and no error msg instead of "You have now run X miles less or more than this time last year".

What would be even better is if the value was 0 then the msg would say "You have run the same miles as this time last year".

I guess this is a simple fix but I can't understand where I'm going wrong.

Please help!

Many thanks!

 

[MARK858]

@sericom, I also think you need to make a change based on post number 7 (I think you amended the code before the edit Ironman did to post 7).

 

[Ironman]

Yes! That worked (edit: thanks MARK858 but it worked anyway). Many thanks!

Just referring back to my first post, could you please tell me what I need to add to the below

Range("MlsY2TDLessLastYr") < 0, "less", "more")

So that if the value is equal to 0, the msg is "equal to" please?

I just tried it and it returned an error "too many arguments".

Thanks again!

 

[sericom]

You could use select case like

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("EntRng")) Is Nothing Or Target.Count > 1 Then Exit Sub
    uValue = Sheets("Training Log").Range("MlsYTDLessLastYr")
    Select Case uValue
        Case Is < 0
            utext = "less"
        Case Is = 0
            utext = "equal to"
        Case Is > 0
            utext = "more"
    End Select
    MsgBox "You have now run " & uValue & " miles " & _
        utext & " than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
    
    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 you reach rank " & (Range("CurYTD").Offset(1, 0).Value) - 1 & " " & vbNewLine & vbNewLine & _
    " (Year end mileage for " & Range("PreYear").Value & ")"), _
    vbInformation, "Year To Date Mileage"
    End If
End Sub

 

[Ironman]

That worked perfectly - thank you so much sericom!