Solved

Adding some code to sort data into macro

Posted on 2008-10-01
3
261 Views
Last Modified: 2012-08-13
The attached spread sheet has a macro which takes data from worksheet 'Stock' and presents the results in Worksheet 'Value'.  I would like four things done to this macro please.

1.  When Macro is run sort data 1-999 in the column with the heading 'Category'
2.  The cell B13 in the 'Value' worksheet keeps appearing in bold I would like it to not be in bold.
3.  The heading 'Value' in worksheet 'Value' to be bold please.
4.  Could the two headings 'Category & Value' both be postioned to the right of the cell they are in.

Any help greatly appreciated
Monthly-Stock-Valuation.xls
0
Comment
Question by:Simonrepro
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 22612510
Hi,

Pls see code and attached workbook below

Cheers

Dave
Option Explicit
Sub values()
    Dim ws1, ws2 As Worksheet
    Dim lastrow, destlast As Long
    Dim cell As Range, myrange As Range
    Set ws1 = Sheets("Stock")
    Set ws2 = Sheets("Value")
    lastrow = ws1.Range("F" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    ws2.Cells.ClearContents
    ws1.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
                                    CopyToRange:=ws2.Range("A1"), Unique:=True
    destlast = ws2.Range("A" & Rows.Count).End(xlUp).Row
    For Each cell In ws2.Range("A2:A" & destlast)
        cell.Offset(0, 1).Formula = "=SUMPRODUCT((Stock!$A$2:$A$" & lastrow & "=A" & cell.Row & ")*((Stock!$F$2:$F$" & lastrow & _
                                    ")),(Stock!$A$2:$A$" & lastrow & "=A" & cell.Row & ")*(Stock!$G$2:$G$" & lastrow & "))"
    Next cell
    Set myrange = ws2.Range([a1], Cells(ws2.Rows.Count, "B").End(xlUp))
    
    ws2.Range("A1").Value = "Category"
    ws2.Range("B1").Value = "Value"
    myrange.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ws2.Range("A1:B1").Font.Bold = True
    ws2.Range("A1:B1").HorizontalAlignment = xlRight
 End Sub

Open in new window

Monthly-Stock-Valuation-ejb-.xls
0
 

Author Closing Comment

by:Simonrepro
ID: 31501872
Very Good Thanks!
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22612534
No probs, thx for the grade :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question