Solved

Adding some code to sort data into macro

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

770 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