View Full Version : [SOLVED:] Seperate unique accounts and manipulate data
blackie42
10-07-2016, 01:10 AM
Hi
Wonder if anyone has time to help create some code for following..
Need to insert a line after every unique account in column K
Need to change the sign on values in AB where value in O is Sell
Need to sum values in AB for each unique account (in AB where the line was inserted)
Have attached before (sheet1) and after (sheet2) if this helps - just a sample, actual sheet is a lot bigger of course
Be really grateful for any help
thanks
Jon
Option Explicit
Sub test()
Dim r As Range
Dim v
Dim i As Long
Set r = Range("k1", Range("k1").End(xlDown)).Resize(, 18)
v = r.Value
For i = 1 To UBound(v)
If v(i, 5) = "Sell" Then v(i, 18) = v(i, 18) * -1
Next
r.Value = v
r.Rows(1).Insert xlDown
Set r = Range("k1").Resize(i, 18)
Application.DisplayAlerts = False
r.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(18), SummaryBelowData:=True
Application.DisplayAlerts = True
r.Rows(1).Delete xlUp
End Sub
blackie42
10-07-2016, 07:59 AM
Hi Mana,
Code is good - any chance you could highlight the accounts that have total as 0.00 in green?
But thanks very much
regards
Jon
Option Explicit
Sub test2()
Dim r As Range
Dim v
Dim i As Long
ActiveSheet.Copy
Set r = Range("k1", Range("k1").End(xlDown)).Resize(, 18)
v = r.Value
For i = 1 To UBound(v)
If v(i, 5) = "Sell" Then v(i, 18) = v(i, 18) * -1
Next
r.Value = v
r.Columns(18).FormatConditions.Add( _
Type:=xlExpression, _
Formula1:="=AND(R[0]C[0]=0,ISBLANK(R[0]C[-13]))") _
.Interior.Color = vbGreen
r.Rows(1).Insert xlDown
Set r = Range("k1").Resize(i, 18)
Application.DisplayAlerts = False
r.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(18), SummaryBelowData:=True
Application.DisplayAlerts = True
r.Rows(1).Delete xlUp
End Sub
blackie42
10-08-2016, 08:31 AM
Thanks Mana
regards
Jon
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.