CODEWITHSUNDEEP
excelvba
Matt Taylor
Matt Taylor

Reputation: 671

Data from array to sheet

I have an array that plots data to a messagebox, how can I also have that same data on a different sheet?

The data I am working with is a linear cable cut list. It looks at the total cable length and the individual lengths and checks to see how many reels of cable I need for all the separate individual lengths.

Ideally Reel 1 lengths would go in column A and the next reel would be column B, so on and so forth with a blank column in between the different cable types.

    'Message Box Output
    For k = LBound(DetStk, 2) To UBound(DetStk, 2)
       sMsg = sMsg & DetStk(1, k) & vbTab & vbTab _
            & DetStk(0, k) & vbCrLf
    Next k

THIS IS WHAT I GOT WORKING WITH HELP FROM "Splintered-Origins-Dev"

    'Sheet Output
    n = 3
    q = rC + p - 6
    For k = LBound(DetStk, 2) To UBound(DetStk, 2)
        If k - 1 >= LBound(DetStk, 2) Then
            If DetStk(0, k - 1) <> DetStk(0, k) Then
                'Data line reset
                n = 3
                p = p + 1
                q = rC + p - 6
                wsVG.Cells(1, q).Value2 = cblType
                wsVG.Cells(2, q).Value2 = DetStk(0, k) & " Reels"
            End If
        Else
            wsVG.Cells(1, q).Value2 = cblType
            wsVG.Cells(2, q).Value2 = DetStk(0, k) & " Reels"
        End If
        wsVG.Cells(n, q).Value2 = DetStk(1, k)
        n = n + 1
    Next k

Upvotes: 1

Views: 204

Answers (2)

Splintered-Origins-Dev
Splintered-Origins-Dev

Reputation: 126

It's not pretty or elegant, but the following code will allow you to output the data going to your message box to a new sheet "Reel Data". Just replace "loopvar" with whatever variable you're using to iterate through each loop. Additionally the "- 1" in the q variable for columns may or may not be needed depending on how your loop variable is set up. I also recommend adding someplace at the beginning of the sub Worksheets("Reel Data").Cells.Clear to wipe out any old data left behind by previous runs and p = 0 to reset the column counter. 

    n = 3 'Row
    q = *loopvar* + p - 1 'Column
    For k = LBound(DetStk, 2) To UBound(DetStk, 2)
        If k - 1 >= LBound(DetStk, 2) Then 'If reel count changes, move over a column
            If DetStk(0, k - 1) <> DetStk(0, k) Then
                n = 3 'Row reset
                p = p + 1 'Increment Column
                q = *loopvar* + p - 1
                Worksheets("Reel Data").Cells(2, q).Value2 = DetStk(0, k)
            End If
        Else
            Worksheets("Reel Data").Cells(2, q).Value2 = DetStk(0, k) 'First Reel
        End If
        Worksheets("Reel Data").Cells(n, q).Value2 = DetStk(1, k)
        n = n + 1 ' Increment Row
    Next k
    p = p + 1 'This will add an extra column before the next iteration

Upvotes: 1

Dy.Lee
Dy.Lee

Reputation: 7567

DetStk Array's 2 rows an k colunms . so You should switch the matrix to make it easier to see.

From

'Message Box Output
For k = LBound(DetStk, 2) To UBound(DetStk, 2)
   sMsg = sMsg & DetStk(1, k) & vbTab & vbTab _
        & DetStk(0, k) & vbCrLf
Next k

to

'Sheet Output
Sheets.Add
Range("a1").Resize(UBound(DetStk, 2) + 1, UBound(DetStk, 1) + 1) = WorksheetFunction.Transpose(DetStk)

Upvotes: 0

Related Questions