Solved

Adding some code to sort data into macro

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
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 …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now