Do calculation & show value in Msgbox

[ipbr21054]

Morning,

I use the code below to transfer values from various cells.
Once the values have been pasted in there new location the last part of the code carries out a calculation.

The calculation looks at the value in cell P35 & compares it with the value of 12570
If the answer is more than 12570 then Currently a Msgbox is shown saying You Are Over £12,570

The above works fine but i now wish to add to this code by telling me how much i am over, also in the same Msgbox

Example.

YOU ARE OVER £12,570 By £3,500

Private Sub JanuaryButton_Click()
If WorksheetFunction.CountA(Range("I9:I18")) = 0 Then
MsgBox "THERE ARE NO VALUES TO TRANSFER", vbCritical, "TRANSFERE RANGE IS EMPTY"

Else

If WorksheetFunction.CountA(Range("E64:E73")) > 0 Then
    answer = MsgBox("CELLS CONTAIN VALUES ALREADY, OVERWRITE THEM ?", vbYesNo + vbCritical, "OVERWRITE CURRENT VALUE MESSAGE")
    If answer = vbNo Then
    Exit Sub
    End If
End If

Range("I9:I18").Copy Destination:=Range("E64:E73")
MsgBox "ALL FIGURES HAVE BEEN TRANSFERED", vbInformation, "MONTHS FIGUES MESSAGE"
Unload SUMMARYSHEETYEAR

Range("H8").ClearContents
Range("I9:I18").ClearContents
Range("I9").Select
End If

    Set Target = Me.Range("P35")
     
    If Target.Value > 12570 Then

        MsgBox "YOU ARE OVER £12,570", vbExclamation, "PERSONAL ALLOWANCE"
        Exit Sub
    End If

End Sub

 

[SunnyAlv]

Like this ?

.....
      If Target.Value > 12570 Then

        MsgBox "YOU ARE OVER £12,570" & " by " & target.value, vbExclamation, "PERSONAL ALLOWANCE"

       Exit Sub
    End If
.....

 

[RoryA]

To show the formatted difference:

        MsgBox "YOU ARE OVER £12,570 by £" & Format$(Target.Value - 12570, "#,##0"), vbExclamation, "PERSONAL ALLOWANCE"

 

[ipbr21054]

@RoryA
That only showed me the value £12,570 & no over value

 

[ipbr21054]

Update.

My mistake & now working thanks

 

[ipbr21054]

Do you see an issue with why its not adding new line

MsgBox "YOU ARE OVER £12,570 & vbNewLine & vbNewLine & BY £" & Format$(Target.Value - 12570, "#,##0"), vbExclamation, "PERSONAL ALLOWANCE"

 

[RoryA]

You put it inside the quotes so it's literal text, not VBA commands. You need:

        MsgBox "YOU ARE OVER £12,570" & vbNewLine & vbNewLine & "BY £" & Format$(Target.Value - 12570, "#,##0"), vbExclamation, "PERSONAL ALLOWANCE"