No problem. Your file already has a blank column E, so we need to
insert 3 more columns then label them.
Sub macrotrythis()
Columns("E:G").Insert Shift:=xlToRight 'insert new columns EFG
'label the new columns
Range("E1").Value = "Txn Type"
Range("F1").Value = "Security Id"
Range("G1").Value = "Security Code-Cash"
Range("H1").Value = "Sec Description"
Columns("H:H").Interior.ColorIndex = 6 'turn column H yellow
'for each row of data, copy column K -> Q, L->R based on column(m)'s
transaction code
For Row = 2 To Range("a1").End(xlDown).Row
TargetRow = Row + 3
Select Case UCase(Trim(Range("M" & Row).Value))
Case "DEBIT": Range("Q" & TargetRow).Value = Range("K" &
Row).Value
Case "CREDIT": Range("R" & TargetRow).Value = Range("K" &
Row).Value
Case Else: Range("S" & TargetRow).Value = Range("K" &
Row).Value
End Select
Next Row
'Columns("M:M").Delete 'remove the leading ' once like the results to
remove column M
Range("R4").Select 'put cursor at R4 when done
'suggestions:
Range("Q1").Value = "Debits"
Range("R1").Value = "Credits"
Range("A1:R1").HorizontalAlignment = xlCenter 'center all of the
column titles
Columns("A:S").AutoFit 'adjust each column width for its data
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
