Simple Excel Macro Lesson

Sharing this VBA code: it is useful to transform or transpose matrices/array of data into records. 

 

 

Sub transpose()
Dim col As Integer

'to get the total rows consisting data
o = Range("a65536").End(xlUp).Offset(1, 0).Row - 1

'clearing up the data within columns AY, BD
Columns("AY:BD").Value = ""

'this is to assign the labels
Range("AY1").Value = "Product"
Range("AZ1").Value = "Shipment date"
Range("BA1").Value = "Quantity"

'row consisting data
therow = 2

'for every rows with data

For i = 2 To Range("a65536").End(xlUp).Offset(1, 0).Row - 1
           
            'for every columns between 2 to 5 ie. Column B to E
            For col = 2 To 5
               
                'call a function to translate the Column number into character
                H = GetCol(col)
                'this is the product
                Range("AY" & therow).Value = Range("a" & i).Value
                'this is the shipping date
                Range("AZ" & therow).Value = DateValue(Range(H & 1).Value)
               
                If Range(H & i).Value = Null Or Range(H & i).Value = 0 Then
                'ignore those with quantity null
                col = col + 1
                Else
                If Range(H & i).Value = "" Then
                Range("ba" & therow).Value = 0
                Else
                'this is the Quantity
                Range("ba" & therow).Value = (Range(H & i).Value)
                End If
                therow = therow + 1
                col = col + 1
                End If
                Next col
Next i

End Sub

Private Function GetCol(Column As Integer) As String
    ' Col is the present column, not the number of cols
    Const A = 65    'ASCII value for capital A
    Dim iMults As Integer
    Dim sCol As String
    Dim iRemain As Integer
    Column = Column - 1
    ' THIS ALGORITHM ONLY WORKS UP TO ZZ. It fails on AAA
    If Column > 701 Then
        GetCol = ""
        Exit Function
    End If
    If Column <= 25 Then
        sCol = Chr(A + Column)
    Else
        iRemain = Int((Column / 26)) - 1
        sCol = Chr(A + iRemain) & GetCol(Column _
            Mod 26)
    End If
    GetCol = sCol
    ColChar = sCol
End Function