One amount to multiple rows as per criteria

[Pinang]

Hi,

Can any one help me to get VBA code to split one cell amount and data to multiple/single rows as per criteria?

Attached data, criteria and output for reference.

 

[Marc L]

Hi, according to your attachment a VBA demonstration as a beginner starter :​

Sub Demo1()
    Dim N&, L&, R&, F
        N = [F1].CurrentRegion.Rows.Count - 2:  If N < 1 Then Beep: Exit Sub
    With [I1].CurrentRegion.Rows
        If .Count > 2 Then .Item("3:" & .Count).Clear
    End With
        L = 2
    With [A1].CurrentRegion.Rows
        For R = 3 To .Count
            F = Evaluate(Replace("IF({1},""=""&ADDRESS(ROW(#),COLUMN(#)))", "#", .Item(R).Resize(, 6).Address))
            F(2) = F(2) & "*$N" & L + 1:  F(5) = "=$F3":  F(6) = "=$G3"
            Rows(L + 1).Resize(N).Columns("I:N").Formula = F
            L = L + N
        Next
    End With
    If L > 2 Then
        With Range("I3:N" & L)
            .Borders.Weight = 2
            .Columns(2).NumberFormat = " #,##0.00_)"
        End With
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Pinang]

Superb! Thanks a lot.

 

[Pinang]

Hi @Marc L,

I have added one more column to this file and made some changes but macro is not working. could you please help on this?

 

[Marc L]

As my demonstration was made upon your original attachment layout so use the same layout or​

rather than a guessing challenge you should describe what « macro is not working » means technically …​

​

 

[Pinang]

I have corrected the macro as per requirements. Thanks Marc L for your time and support.

 

[p45cal]

This can be done without VBA; Power Query was designed for just this sort of thing.
See table at cell J2 in the attached. Alter the data in the 2 tables on the left, right-click any cell in the result table and choose Refresh.
Dates are converted to proper Excel dates too.