PDA

View Full Version : Solved: Create string for Messagebox



rama4672
09-06-2004, 10:58 AM
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
09-06-2004, 11:28 AM
Do you want the Sum of the range?


MsgBox Application.WorksheetFunction.Sum(temp)

tinyjack
09-06-2004, 11:28 AM
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
09-06-2004, 11:34 AM
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
09-06-2004, 11:44 AM
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
09-06-2004, 12:02 PM
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
09-06-2004, 01:10 PM
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
09-06-2004, 01:30 PM
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
09-06-2004, 02:32 PM
Thanks again TJ.
That did the trick

Ian

mdmackillop
07-14-2005, 11:06 AM
Retitled

sheeeng
07-15-2005, 08:27 AM
Great code here..
I benefit from them.