Hi Johan,

Am Thu, 28 Mar 2019 00:40:25 -0700 (PDT) schrieb :

I'm struckling to get 'OnError resume next' to include (or something like that).

If the cell in column A is empty or there is no splitcode like ";" then the macrocode has to skip this record en go on with the next one.

For example;
A1= AA123;BB123 Then run macrocode
A2= (empty cell, skip running the macrocode take next record)
A3= CC123;DD123 Then run macrocode
A4= EE123 (no splitcode, skip running the macrocode take next record)

try:

Sub Test()
Dim varData1 As Variant, varData2 As Variant, varTmp As Variant
Dim LRow As Long, i As Long, n As Long

n = 1
'Modify sheet name here
With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'Modify the range here
varData1 = .Range("A1:A" & LRow)
varData2 = .Range("B1:D" & LRow)
For i = LBound(varData1) To UBound(varData1)
If Len(varData1(i, 1)) = 0 Then
.Cells(n, 1) = ""
.Cells(n, 2).Resize(, 3) = Application.Index(varData2, i)
n = n + 1
GoTo Skip
End If
varTmp = Split(varData1(i, 1), ";")
If UBound(varTmp) 0 Then
.Cells(n, 1).Resize(UBound(varTmp) + 1) = _
Application.Transpose(varTmp)
.Range(.Cells(n, 2), .Cells(n + UBound(varTmp), 4)) _
= Application.Index(varData2, i)
n = n + UBound(varTmp) + 1
Else
.Cells(n, 1) = varTmp
.Cells(n, 2).Resize(, 3) = Application.Index(varData2, i)
n = n + 1
GoTo Skip
End If
Skip:
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016