PDA

View Full Version : Formatting excel sheet



Lartk
10-16-2012, 08:58 AM
Please see the attached sheet. Sheet "Current" has data as it appears now. Sheet "Results" is how I would like the data to appear using a vba code to get it that way. I have a code now that can separate each different cusip in column G (so it can insert a row between line 20 and 21.) However, within each cusip I need to divide up the buys and sells. Please see the results sheet and let me know if you can help me manipulate the data in that way with a vba code.

Thank you.

stanleydgrom
10-16-2012, 06:29 PM
Lartk,

Welcome to the VBA Express forum.


It would appear that some of the columns in worksheet Results have shifted to the right???


In the below macro, in worksheet Current, I am using column AO as a work area.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).




Option Explicit
Sub ReorgData()
' stanleydgrom, 10/16/2012
' http://www.vbaexpress.com/forum/showthread.php?t=44015
Dim r As Long, lr As Long
Application.ScreenUpdating = False
Worksheets("Current").Activate
lr = Cells(Rows.Count, "G").End(xlUp).Row
With Range("AO9:AO" & lr)
.FormulaR1C1 = "=RC[-34]&RC[-33]"
.Value = .Value
End With
With Cells(lr, 5)
.Value = 1
.NumberFormat = "General"
End With
Cells(lr, 11).Value = "p"
For r = lr To 10 Step -1
If Cells(r, 41) <> Cells(r - 1, 41) Then
Rows(r).Insert
With Cells(r - 1, 5)
.Value = 1
.NumberFormat = "General"
End With
Cells(r - 1, 11).Value = "p"
End If
Next r
lr = Cells(Rows.Count, "G").End(xlUp).Row
Range("AO9:AO" & lr).ClearContents
Application.ScreenUpdating = True
End Sub




Then run the ReorgData macro.


Have a great day,
Stan

p45cal
10-16-2012, 06:45 PM
try:Sub blah()
LR = Cells(Rows.Count, "G").End(xlUp).Row
For rw = LR To 9 Step -1
If (Cells(rw, "G").Value <> Cells(rw + 1, "G").Value) Or (Cells(rw, "H").Value <> Cells(rw + 1, "H").Value) Then
Cells(rw, "E").NumberFormat = "General": Cells(rw, "E").Value = 1
Cells(rw, "K").Value = "p"
Rows(rw + 1).Insert
End If
Next rw
End Sub
I see there's also been a couple of columns inserted, but I don't know just which.

Lartk
10-17-2012, 08:24 AM
Ok, i fixed the columns so they match in the current sheet and results sheet. The below macro works, so can we just adjust the column references to make it work with the attached?




Option Explicit
Sub ReorgData()
Dim r As Long, lr As Long
Application.ScreenUpdating = False
Worksheets("Current").Activate
lr = Cells(Rows.Count, "G").End(xlUp).Row
With Range("AO9:AO" & lr)
.FormulaR1C1 = "=RC[-34]&RC[-33]"
.Value = .Value
End With
With Cells(lr, 5)
.Value = 1
.NumberFormat = "General"
End With
Cells(lr, 11).Value = "p"
For r = lr To 10 Step -1
If Cells(r, 41) <> Cells(r - 1, 41) Then
Rows(r).Insert
With Cells(r - 1, 5)
.Value = 1
.NumberFormat = "General"
End With
Cells(r - 1, 11).Value = "p"
End If
Next r
lr = Cells(Rows.Count, "G").End(xlUp).Row
Range("AO9:AO" & lr).ClearContents
Application.ScreenUpdating = True
End Sub

p45cal
10-17-2012, 08:33 AM
Ok, i fixed the columns so they match in the current sheet and results sheet. The below macro works, so can we just adjust the column references to make it work with the attached?


The code I submitted in message#3 needs no alteration and already works for the test.xls workbook.

Lartk
10-17-2012, 08:35 AM
Your right, this code works great!



Sub blah()
LR = Cells(Rows.Count, "G").End(xlUp).Row
For rw = LR To 9 Step -1
If (Cells(rw, "G").Value <> Cells(rw + 1, "G").Value) Or (Cells(rw, "H").Value <> Cells(rw + 1, "H").Value) Then
Cells(rw, "E").NumberFormat = "General": Cells(rw, "E").Value = 1
Cells(rw, "K").Value = "p"
Rows(rw + 1).Insert
End If
Next rw
End Sub


Thank you for the help!