Snasui Excel Forum

Code: Select all

Sub Test()
Dim Id&, Bc&, n&, I, B
    Id = Sheets("ID STORE").Cells(Rows.Count, 1).End(xlUp).Row - 1
    Bc = Sheets("Barcode").Cells(Rows.Count, 1).End(xlUp).Row - 1
    n = Id * Bc
    I = Application.Index(Sheets("ID STORE").Columns(1), Evaluate("(row(1:" & n & ")-1)/" & Bc & "+2"))
    B = Application.Index(Sheets("Barcode").Columns(1), Evaluate("MOD(ROW(1:" & n & ")-1," & Bc & ")+2"))
    Sheets("Compare").[a2].CurrentRegion.Offset(1).ClearContents
    Sheets("Compare").[a2].Resize(n, 2) = Application.Choose(Array(1, 2), I, B)
End Sub

Code: Select all

Sub Test0()
    Dim arrStore As Variant, arrBarcode As Variant, arrStB As Variant
    Dim m As Long, n As Long, l As Long
    With Worksheets("ID STORE")
        arrStore = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
    End With
    With Worksheets("Barcode")
        arrBarcode = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
    End With
    ReDim arrStB(1 To UBound(arrStore) * UBound(arrBarcode), 1 To 2)
    l = 1
    For m = 1 To UBound(arrStore)
        For n = 1 To UBound(arrBarcode)
            arrStB(l, 1) = arrStore(m, 1)
            arrStB(l, 2) = arrBarcode(n, 1)
            l = l + 1
        Next n
    Next m
    With Worksheets("Compare")
        .Range("a2").CurrentRegion.Offset(1, 0).ClearContents
        .Range("a2").Resize(l - 1, 2).Value = arrStB
    End With
    MsgBox "Finished.", vbInformation
End Sub