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

old macro code chnages

ushaanu

Member
HI All ,

happy new year ,

I have one old macro file with me where I convert raw sheet data in required sequence but now in raw sheet some row sequence have been change now I need help to change in macro so that convert raw sheet data to Required results.

New sequence in Raw File

thanks in advance for you help .

Regards
Anu
New sequence in Raw File
columnF
columnV
columnX
columnAD
columnAF
 

Attachments

Last edited:

Marc L

Excel Ninja
Hi !​
No need to modify the VBA procedure when smart people use a 'Settings' worksheet in order to parameter the sequence order !​
Anyway according to your attachment a starter demonstration (v3) :​
Code:
Sub Demo1()
  Const F = """&{"""";""-G3"";""-G4"";""-H3"";""-H4"";""-G"";""-H""}", S = "IF({1},SUMIF(B¤:B#,""*""&RIGHT(B§:Bµ,2)&""*"",C¤:C#))"
    Dim V, N&, L&, R&
        V = [{6;22;24;30;32}]
        N = UBound(V) + 2
        L = 3
        Sheet2.UsedRange.Offset(2).Clear
        Application.ScreenUpdating = False
    With Sheet1.[A1].CurrentRegion.Rows
        For R = 4 To .Count
           .Cells(R, 1).Copy Sheet2.Cells(L, 4).Resize(N)
           .Cells(R, 4).Copy Sheet2.Cells(L, 1).Resize(N)
            Sheet2.Cells(L, 2).Resize(N).Value2 = Evaluate("""" & .Cells(R, 4).Text & F)
            Sheet2.Cells(L, 3).Resize(UBound(V)).Value2 = Application.Index(.Item(R), , V)
            L = L + N
            Sheet2.Cells(L - 2, 3).Resize(2).Value2 = Sheet2.Evaluate _
                        (Replace(Replace(Replace(Replace(S, "¤", L - N), "#", L - 3), "§", L - 2), "µ", L - 1))
        Next
    End With
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Top