I have an Excel sheet which contains following content:
I have worked on a macro which does following:-
- Find the column having Header ABC
- Insert two new columns adjacent to ABC with name of AAA and BBB
- Then split the ABC cell content into respective cells of AAA and BBB; note (ABC column may have one one line in some cases )
- Follow step (3) till end of column ABC content.
End result should look like this:
I have written following code :-
Sub Num() Dim rngDHeader As Range Dim rngHeaders As Range Set rngHeaders = Range("1:1") 'Looks in entire first row; adjust as needed. Set rngDHeader = rngHeaders.Find("ABC") Sub sbInsertingColumns() 'Inserting a Column at Column B rngDHeader.EntireColumn.Insert 'Inserting 2 Columns from C rngDHeader.EntireColumn.Insert Dim rngDHeader As Range Dim sText As String Dim aText As Variant 'array Dim i As Long 'number of array elements Set rngDHeader = Sheets("Sheet1").Range("C2") Do Until rng = "" 'split the text on carriage return character chr(10) aText = Split(rngDHeader.Value, Chr(10)) 'get the number of array elements i = UBound(aText) 'build the output text string sText = aText(i - 2) & Chr(10) _ & aText(i - 1) & Chr(10) _ & aText(i) 'output rngDHeader.Offset(, 1) = sText Set rngDHeader = rngDHeader.Offset(1, 0) Loop Set rngDHeader = Nothing End Sub
Can anyone help me with this?