please help...

[debra2468]

hi all,
i am looking out for a macro code which can sort a column in ascendin
order..
i have a column K which contains positive and negative values..i nee
to sort this column in ascending order..then i have to display th
grand totals of the positive and negative values by inserting rows an
bold these totals....

can someone please help me out in this

 

[Bob Phillips]

Record a macro whilst sorting the data, it will give you what you need.

At the end add these formulae to a couple of cells

=SUMIF(A:A,"<0")
=SUMIF(A:A,">=0")

it will all be there

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[debra2468]

hey bob,
i already have a macro which is doing a lot of other things..in thi
macro,i need to incorporate this code...

a) sort column K, which contains positive and negative values, i
ascending order..

b) display the grand totals of the positive and negative values b
inserting rows and then bold these totals....

how can i accomplish this

 

[frank stone]

try this
Range("K2").Select
Selection.Sort Key1:=Range("K1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Set item1 = Range("K2")
Do While Not IsEmpty(item1)
Set item2 = item1.Offset(1, 0)
If item1 < 0 Then
Set item1 = item2
Else
item1.EntireRow.Insert
item1.EntireRow.Insert
Exit Do
End If
Loop
Range("K2").End(xlDown).Offset(1, 0).Select
With ActiveCell
Set Rng = Range(.Offset(-1, 0), .Offset(-1, 0).End
(xlUp))
.Formula = "=Sum(" & Rng.Address & ")"
End With
ActiveCell.Offset(2, 0).Select
ActiveCell.End(xlDown).Offset(1, 0).Select
With ActiveCell
Set Rng = Range(.Offset(-1, 0), .Offset(-1, 0).End
(xlUp))
.Formula = "=Sum(" & Rng.Address & ")"
End With

 

[Bob Phillips]

Debra,

If you record a macro as I said earlier, you can either merge this code into
that, or call the new macro from your old code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)