Converting MsgBox to a new worksheet 1

[aperez1981]

I have the following code for a msgbox
Msg = "The summary measures for product " & Product & " in " & _
Region & " are:" & vbCrLf & vbCrLf
If CountOpt Then Msg = Msg & "Count: " & _
DRange.Cells.Count & vbCrLf
If AverageOpt Then Msg = Msg & "Average: " & _
Format(Application.Average(DRange), "0.00&quot & vbCrLf
If MedianOpt Then Msg = Msg & "Median: " & _
Format(Application.Median(DRange), "0.00&quot & vbCrLf
If StdevOpt Then Msg = Msg & "Std. dev.: " & _
Format(Application.StDev(DRange), "0.00&quot & vbCrLf
If MinOpt Then Msg = Msg & "Minimum: " & _
Application.Min(DRange) & vbCrLf
If MaxOpt Then Msg = Msg & "Maximum: " & _
Application.Max(DRange)
MsgBox Msg, vbInformation, "Summary Information"
I want to keep the msgbox and also place the information on a new sheet called report. I have gotten this far:
Sheet = "The summary measures for product " & Product & " in " & Region & " are:" & _

If CountOpt Then Sheet = Sheet & "Count: " & _
DRange.Cells.Count & vbCrLf & ActiveCell.Offset(1, 0)
If AverageOpt Then Sheet = Sheet & "Average: " & _
Format(Application.Average(DRange), "0.00&quot & ActiveCell.Offset(1, 0) & vbCrLf
If MedianOpt Then Sheet = Sheet & "Median: " & _
Format(Application.Median(DRange), "0.00&quot & ActiveCell.Offset(1, 0) & vbCrLf
If StdevOpt Then Sheet = Sheet & "Std. dev.: " & _
Format(Application.StDev(DRange), "0.00&quot & ActiveCell.Offset(1, 0) & vbCrLf
If MinOpt Then Sheet = Sheet & "Minimum: " & _
Application.Min(DRange) & ActiveCell.Offset(1, 0) & vbCrLf
If MaxOpt Then Sheet = Sheet & "Maximum: " & _
Application.Max(DRange)
With Sheets("Report&quot.Range("A1&quot
.Value = Sheet
The thing is that everything is in the first cell. I would like to get each option in a new row by it self.

Thanks

 

[cthaxter]

Instead of the second half of your code, i.e., the part starting with

Sheet = "The summary measures for product"...

Replace it with the following, being sure that the active cell is the one where you want to start placing your results.

ActiveCell.Value = "The summary measures for product " & Product & " in " & Region & " are:"
ActiveCell.Offset(1, 0).Select

If countopt Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Count: " & _
drange.Cells.Count
End If
If AverageOpt Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Average: " & _
Format(Application.Average(drange), "0.00&quot & _
ActiveCell.Offset(1, 0)
End If
If MedianOpt Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Median: " & _
Format(Application.Median(drange), "0.00&quot & _
ActiveCell.Offset(1, 0)
End If
If stdevopt Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Std. dev.: " & _
Format(Application.StDev(drange), "0.00&quot
End If
If MinOpt Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Minimum: " & _
Application.Min(drange) & ActiveCell.Offset(1, 0)
End If
If maxopt Then
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "Maximum: " & _
Application.Max(drange)
End If

 

[aperez1981]

thank you so very much!! it works great!

 

[cthaxter]

You're welcome. Oh, and one thing I neglected, which you may have discovered already:

Delete the following code from the three places where it occurs:

& ActiveCell.Offset(1, 0)

It's in the IF statements for AverageOpt, MedianOpt, and MinOpt. It's unnecessary, and may add extra blank characters or something. I didn't use these variables as examples when I tested it, so I forgot to fix those statements.

Enjoy!