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!

 

[sericom]

Try

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

 

[Ironman]

Many thanks sericom.

I amended the code to the below:

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

And I'm getting a syntax error (the msgbox code is in red)

 

[sericom]

Your If needs to be IIF

MsgBox "You have now run " & .Range("MlsYTDLessLastYr") & "miles " & _
         IIf(.Range("MlsYTDLessLastYr") < 0, " less", " more") & _
        " than this time last year   ", vbInformation, "Mileage Compared To This Time Last Year"

 

[Ironman]

Thanks again.

As you suggested, I amended it to the below:

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


Now I'm getting "Error 1004: Application-Defined or Object-Defined Error" (All the code highlighted yellow)


I don't think this makes a difference, but there are 2 lines of code that precede the above, which are as below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("EntRng")) Is Nothing Or Target.Count > 1 Then Exit Sub

 

[sericom]

Are you sure you have a named range MlsYTDLessLastYr on worksheet Daily Tracking?

 

[Ironman]

Ahhhh - no, it's on a sheet titled "Exercise Log" - I thought as it was a named range with a scope of "Workbook" it didn't matter to the code which sheet it was on.

 

[sericom]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("EntRng")) Is Nothing Or Target.Count > 1 Then Exit Sub
    MsgBox "You have now run " & Sheets("Training Log").Range("MlsY2TDLessLastYr") & " miles " & _
        IIf(Sheets("Training Log").Range("MlsY2TDLessLastYr") < 0, "less", "more") & _
        " than this time last year ", vbInformation, "Mileage Compared To This Time Last Year"
End Sub

 

[Ironman]

Thanks again.

I tried your code with and without:

With Worksheets("Daily Tracking") and End With

and I still get Error 1004 with the same code highlighted in yellow.

I didn't use End Sub as there is further code in the Sub as below, which runs OK

MsgBox "You have now run " & Sheets("Training Log").Range("MlsY2TDLessLastYr") & " miles " & _
IIf(Sheets("Training Log").Range("MlsY2TDLessLastYr") < 0, "less", "more") & _
" than 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 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

 

[sericom]

Sorry, MlsY2TDLessLastYr should be MlsYTDLessLastYr