Macro for displaying messages using values from a different worksheet

[Pantherlucky]

Hello! I need to create a macro to create a message box which will contain the names and the grades of 8 different candidates. These values are located in a different worksheet (Sheet 2). For the first 2 candidates the message must be "*insert name of candidate here* is accepted to Department A with a grade of *insert grade here*". For the next 2 the message must be "*insert name of candidate here* is accepted to Department B with a grade of *insert grade here*". For the next candidate the message is "*insert name of candidate here* is accepted to Department C with a grade of *insert grade here*". Finally for the last three candidates the message must be " *insert name of candidate here* is a back up candidate*". The two columns which are used for this are, as i mentioned, in Sheet2 and are sorted in a descending order ( top to lowest grade). The A column contains the name of the candidates whilst the B column contains their respective grades. Any help would be greatly appreciated!

 

[mumps]

This macro assumes you have headers in row 1 and the names start in row 2.

Sub MessageBoxes()
    MsgBox (Range("A2") & " is accepted to Department A with a grade of " & Range("B2") _
        & Chr(10) & Range("A3") & " is accepted to Department A with a grade of " & Range("B3"))
    MsgBox (Range("A4") & " is accepted to Department B with a grade of " & Range("B4") _
        & Chr(10) & Range("A5") & " is accepted to Department B with a grade of " & Range("B5"))
    MsgBox (Range("A6") & " is accepted to Department C with a grade of " & Range("B6"))
    MsgBox (Range("A7") & ", " & Range("A8") & " and " & Range("A9") & " are back up candidates.")
End Sub

 

[Pantherlucky]

This macro assumes you have headers in row 1 and the names start in row 2.

Sub MessageBoxes()
    MsgBox (Range("A2") & " is accepted to Department A with a grade of " & Range("B2") _
        & Chr(10) & Range("A3") & " is accepted to Department A with a grade of " & Range("B3"))
    MsgBox (Range("A4") & " is accepted to Department B with a grade of " & Range("B4") _
        & Chr(10) & Range("A5") & " is accepted to Department B with a grade of " & Range("B5"))
    MsgBox (Range("A6") & " is accepted to Department C with a grade of " & Range("B6"))
    MsgBox (Range("A7") & ", " & Range("A8") & " and " & Range("A9") & " are back up candidates.")
End Sub

I want to do exactly this but the columns that i need to use are on Sheet2. Using this code, it runs it on Sheet1. Is there a fix for this?

 

[mumps]

Try:

Sub MessageBoxes()
    With Sheets("Sheet2")
        MsgBox (.Range("A2") & " is accepted to Department A with a grade of " & Range("B2") _
            & Chr(10) & .Range("A3") & " is accepted to Department A with a grade of " & .Range("B3"))
        MsgBox (.Range("A4") & " is accepted to Department B with a grade of " & .Range("B4") _
            & Chr(10) & .Range("A5") & " is accepted to Department B with a grade of " & .Range("B5"))
        MsgBox (.Range("A6") & " is accepted to Department C with a grade of " & .Range("B6"))
        MsgBox (.Range("A7") & ", " & .Range("A8") & " and " & .Range("A9") & " are back up candidates.")
    End With
End Sub

Also, to avoid clutter, click the "Reply" button instead of the "Reply With Quote" button.

 

[Pantherlucky]

Hey i have a small question for the code that you sent me earlier. So there seems to be one small bug...(excuse the weird letters, those are supposed to be greek)

With Sheets("Sheet2")
MsgBox (.Range("A1") & " äåêôüò óôï ôìÞìá Á ìå óõíïëéêÜ ìüñéá, " & Range("B1") _
& Chr(10) & .Range("A2") & " äåêôüò óôï ôìÞìá Á ìå óõíïëéêÜ ìüñéá, " & .Range("B2"))
MsgBox (.Range("A3") & " äåêôüò óôï ôìÞìá  ìå óõíïëéêÜ ìüñéá, " & .Range("B3") _
& Chr(10) & .Range("A4") & " äåêôüò óôï ôìÞìá  ìå óõíïëéêÜ ìüñéá, " & .Range("B4"))
MsgBox (.Range("A5") & " äåêôüò óôï ôìÞìá à ìå óõíïëéêÜ ìüñéá, " & .Range("B5"))
MsgBox (.Range("A6") & " Åðéëá÷þí ")
MsgBox (.Range("A7") & " Åðéëá÷þí ")
MsgBox (.Range("A8") & " Åðéëá÷þí ")
End With

The result of "Range("B1") displays the result of the B column, first row of Sheet1 instead of Sheet2 which was assigned via With Sheets("Sheet2"). This is fairly weird and i am wondering if you have an explanation for this. Thanks again for the help!

 

[mumps]

Sub MessageBoxes()
    With Sheets("Sheet2")
        MsgBox (.Range("A2") & " is accepted to Department A with a grade of " & .Range("B2") _
            & Chr(10) & .Range("A3") & " is accepted to Department A with a grade of " & .Range("B3"))
        MsgBox (.Range("A4") & " is accepted to Department B with a grade of " & .Range("B4") _
            & Chr(10) & .Range("A5") & " is accepted to Department B with a grade of " & .Range("B5"))
        MsgBox (.Range("A6") & " is accepted to Department C with a grade of " & .Range("B6"))
        MsgBox (.Range("A7") & ", " & .Range("A8") & " and " & .Range("A9") & " are back up candidates.")
    End With
End Sub

I missed entering a period in .Range("B2")