My Solution: Search thorugh each row in the table fro a value larger than zero (anything but zero or empty) , for each hit write a line in a textfile with the supplier, article and cell value. Then use the text file to create your junction table.
The code
Sub ConvertTable()
Dim str, strForb As String
Dim col As Integer
Sheets("Sheet1").Select
Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("Rows.txt")
For i = 2 To 5 'enter which row that contains data
strArticle = Range("A" & i).Value
For Each Cell In Range("B" & i & ":E" & i)
If Cell.Value > 0 Then
col = Cell.Column
str = strForb & "," & Range(ColNo2ColRef(col) & 1) & "," & Cell.Value
objFile.WriteLine (str & cbrlf)
End If
Next Cell
Next i
objFile.Close
End Sub
Function ColNo2ColRef(ColNo As Integer) As String
If ColNo <> 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function
