Create string for Messagebox

[rama4672]

I have the following code, what i need it to do is when it gets to the message box i want it to display in the box the value of the range a2:k5
it goes through everthing ok untill it gets to the msgbox that is where i get the error.

Option Explicit

Sub sortorder()
' sortorder Macro
Dim temp As range
Set temp = ActiveSheet.range("a2:k5")
range("B1:K201").Select
Selection.AutoFilter
Selection.Sort Key1:=range("K1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
MsgBox (temp)
Selection.Sort Key1:=range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.AutoFilter
range("B2").Select
End Sub

Thank you if anyone can help

Ian

[Jacob Hilderbrand]

Do you want the Sum of the range?

MsgBox Application.WorksheetFunction.Sum(temp)

[tinyjack]

I think it depends on what you mean by the value of the range. Do you want a total of the cells in the range or to list the contents of the range?

TJ

[Zack Barresse]

If you're looking for the sum of the range, as DRJ has shown, you can leave your msgbox the same and change your first two lines to ...

Dim temp As Long
    temp = WorksheetFunction.Sum(ActiveSheet.Range("A2:K5"))

But if you're just looking for it to go into a msgbox, DRJ's way is the simpler of the two and you can rid yourself of those 2 lines altogether. (Except your Dim, if you're using Option Explicit).

[rama4672]

thanks drj,tinyjack and firefytr for the replys
What i want is the list of contents of the range, so it would tell me the name and then in each column there would be points that i also need to be listed
i only need it to list the top 4 results

i have attached the file it relates to

Thanks

Ian

[tinyjack]

You need to build a string to display the list, something like:

Dim strMsg as String
Dim lngRow as Long
Dim rngCell as Range
'Do your sorting etc.
For Each rngCell in temp
      If rngCell.Row <> lngRow Then
         strMsg = strMsg & Chr(10) & rngCell.Value
         lngRow = rngCell.Row
      Else
         strMsg = strMsg & " : " & rngCell.Value
      End If
   Next
MsgBox strMsg

HTH

TJ

[rama4672]

Thank you for that TJ

That works great, but there is a few things that i would like to change, when it displays the msgbox can i get it to put a number before the data ie: 1 for the first row then 2 then 3

Thanks

Ian

[tinyjack]

strMsg = strMsg & Chr(10) & rngCell.Value

changed to

strMsg = strMsg & Chr(10) & (rngCell.Row - 1) & " : " & rngCell.Value

Should do the trick for you.

TJ

[rama4672]

Thanks again TJ.
That did the trick

Ian

[mdmackillop]

Retitled

[sheeeng]

Great code here..
I benefit from them.