Arrange File Data Using VBA.

[Warren13]

Hi,

I would like to arrange my data of rejects item from Sheet 1 (REJECT FILE FORM1) and make it appear as in Sheet2 (REJECT FILE FORM2). Kindly refer to the below attachment.

PS. I cant attached excel file so it just a screenshot. Picture 1 should be the data on Sheet1(REJECT FILE FORM1) and Picture 2 should be the result data after clicking "Button Here".

Thank You.

 

[jindon]

Not tested

Sub test()
    Dim a, b, i As Long, ii As Long, n As Long
    a = [a2].CurrentRegion.Value
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 4)
    n = 1: b(n, 1) = a(1, 1): b(n, 2) = a(1, 2)
    b(n, 3) = "Quantitye": b(n, 4) = "Reason"
    For i = 2 To UBound(a, 1)
        For ii = 3 To UBound(a, 2)
            If a(i, ii) <> "" Then
                n = n + 1
                b(n, 1) = a(i, 1): b(n, 2) = a(i, 2)
                b(n, 3) = a(i, ii): b(n, 4) = a(1, ii)
            End If
    Next ii, i
    Sheets.Add.Cells(1).Resize(n, 4).Value = b
End Sub

 

[Warren13]

It works. . . ! Thank you very much for your help, Jindon.