none
Microsoft Excel Output total to new sheet with multiple cells RRS feed

  • Question

  • How do I change the output from a message board to have it output on to a new sheet

    'Output totals to a message box
       sTtl = "Total stock at " & dStk & " = " & TotStk
          
        sMsg = "Board No." & vbTab & "Cut Lenght" & vbCrLf
      
        For k = LBound(DetStk, 2) To UBound(DetStk, 2)
           sMsg = sMsg & DetStk(0, k) & vbTab & vbTab _
                & DetStk(1, k) & vbCrLf
        Next k
      
        MsgBox sMsg, vbOKOnly, sTtl

    End Sub

    Tuesday, March 14, 2017 2:28 PM

All replies

  • Hi Lwhetham,

    from your code above I can understand that you want to add the result to worksheet instead of displaying msgbox.

    you can try to create a object of sheet and then try to find the last cell in the column and add the data there.

    some thing like below.

    Sub demo()
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim sTtl As String
    sTtl = "Total stock at Shop = 100"
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1
    sht.Cells(LastRow, 1).Value = sTtl
    End Sub

    so the data will be added to the sheet like below.

    you can modify the code as per your requirement.

    to insert in a multiple cell like below.

    Sub demo()
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim sTtl, bno, cl As String
    sTtl = "Total stock at Shop = 100"
    bno = "Board No = 50"
    cl = "cut length = 10"
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1
    sht.Cells(LastRow, 1).Value = sTtl
    sht.Cells(LastRow, 2).Value = bno
    sht.Cells(LastRow, 3).Value = cl
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, March 15, 2017 2:44 AM
    Moderator