Hi,
Below is some code I have managed to develop so far for a set task. However I am now stuck and any help guidance or advice would be very much appreciated. I have attached the worksheet, which is where the coded below delivers me so far.
I now need return a negative figure in column G, when column E's value is a "3".
I then need to split the data by column E (either a 1 or 3) and total the values in columns G. Returning two total values under each set of data.
Then if at all possible return, as a net figure, under the data a total of all data.
The data in this report will vary day to day, so I'm a little stuck on how to do this. Any help you guys can give will be very very gratefull.
Attached is the code I have so far.
Sub DPF_1()
'
' DPF_1 Macro
'
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(6, 2), Array(10, 2), Array(17, 2), Array(18, 2), _
Array(19, 2), Array(31, 1), Array(42, 2), Array(56, 2), Array(75, 2), Array(81, 2), Array( _
93, 2), Array(99, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=-18
Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E1:E54") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Rows("1:54")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
Cells.Select
Cells.EntireColumn.AutoFit
End With
ActiveWindow.SmallScroll Down:=-42
Columns("G:G").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ActiveWindow.SmallScroll Down:=-15
End Sub
And attached is a copy (with disguised data) of how the results look so far. And also a copy of how I would like the data to look when in a finished status.
MacroTestData.xlsx MacroTestData2.xlsx
Hopefully this makes sense. Thanks in advnace for anyone that can help with any part of this for me.
Kevin p
Bookmarks