Solved

Adding some code to sort data into macro

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

746 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

14 Experts available now in Live!

Get 1:1 Help Now