Solved

Adding some code to sort data into macro

Posted on 2008-10-01
3
260 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

808 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