Rearranging Excel Cell based on Value [closed]

by

This sub procedure works with two variant arrays.

Option Explicit

Sub Macro3()
    Dim i As Long, j As Long, nr As Long
    Dim tmp As Variant, arr As Variant, hdr As Variant, vals As Variant

    With Worksheets("sheet4")
        tmp = .Cells(1, "A").CurrentRegion
        ReDim vals(LBound(tmp, 1) To UBound(tmp, 1), LBound(tmp, 2) To UBound(tmp, 2))
        nr = UBound(tmp, 1) + 2

        For i = LBound(tmp, 1) To UBound(tmp, 1)
            vals(i, 1) = tmp(i, 1)
            For j = LBound(tmp, 2) + 1 To UBound(tmp, 2)
                If CBool(InStr(1, tmp(i, j), Chr(58), vbBinaryCompare)) Then
                    arr = Split(tmp(i, j), Chr(58))
                    arr(0) = Trim(arr(0)): arr(1) = Trim(arr(1))
                    hdr = Application.Match(arr(0), .Rows(nr), 0)
                    If IsError(hdr) Then
                        hdr = .Cells(nr, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
                        .Cells(nr, hdr) = arr(0)
                        If UBound(vals, 2) < hdr Then
                            ReDim Preserve vals(LBound(tmp, 1) To UBound(tmp, 1), LBound(tmp, 2) To hdr)
                        End If
                    End If
                    vals(i, hdr) = arr(1)
                End If
            Next j
        Next i

        .Cells(nr + 1, "A").Resize(UBound(vals, 1), UBound(vals, 2)) = vals
    End With

End Sub

enter image description here

More Related Contents:

  1. to make this code simpler
  2. VBA – How to grab the cell below if a cell has text?
  3. Excel VBA – A general how to
  4. VBA code required for Msg Box when two value equal in two cells
  5. Find text and show value if condition is met
  6. Excel check len string separated by comma [closed]
  7. How can I insert variable into formula in VBA
  8. Wait until ActiveWorkbook.RefreshAll finishes – VBA
  9. Code in VBA loops and never ends. How to fix this?
  10. Excel column number from column name
  11. Pop up the Excel Statusbar?
  12. VBA – destroy a modeless UserForm instance properly
  13. How To Speed Up VBA Code
  14. Execute a command in command prompt using excel VBA
  15. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  16. Copy data from another Workbook through VBA
  17. Excel VBA Load worksheet in Function
  18. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  19. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  20. Load csv file into a VBA array rather than Excel Sheet
  21. Insert line break in wrapped cell via code
  22. Change color of certain characters in a cell
  23. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  24. VBA Macro crashes after 32000 rows
  25. How to create dynamic variable names VBA
  26. How to break a long string into multiple lines
  27. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  28. simple vba code gives me run time error 91 object variable or with block not set
  29. Remove double quotation from write statement in vba
  30. How to improve the speed of VBA macro code?

Leave a Comment