+ Reply to Thread
Results 1 to 2 of 2

Code to create result based on differnt cell ref. Split and total columns.

  1. #1
    Registered User
    Join Date
    06-12-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Code to create result based on differnt cell ref. Split and total columns.

    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
    Last edited by Kevin P; 09-20-2012 at 11:33 AM.

  2. #2
    Registered User
    Join Date
    06-12-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Code to create result based on differnt cell ref. Split and total columns.

    Apologies. Missed from previous e-mail.


    MacroTestData.xlsx


    Thanks again.

    Kevin P

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1