• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

One amount to multiple rows as per criteria

Pinang

Member
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.
 

Attachments

  • One to multiple splits VBA.xlsb
    10.2 KB · Views: 9
Hi, according to your attachment a VBA demonstration as a beginner starter :​
Code:
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 !
 
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?
 

Attachments

  • One to multiple splits VBA.xlsb
    18.2 KB · Views: 2
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 …​
 
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.
82594
 

Attachments

  • Chandoo50762One to multiple splits VBA.xlsx
    21.3 KB · Views: 1
Back
Top