Search : in
By :

Excel Macro help

Last answer on Apr 2, 2009 12:43:04 am BST DDTEmp, on Mar 27, 2009 3:56:13 pm GMT 
 Report this message to moderators

Hello,
I am new, and know next to nothing about Macros in excel, and I was wondering if anyone could help me out.

I do a report every week, and it is extremely complicated, but I don't think it has to be that way.
What the report consits of is something like this....
I copy a table from microsoft access, and paste into excel. From there I am supposed to seperate certain information. Lets use cars as examples.
I would have an excel sheet that looked something like this

VW 09
VW 08
VW 09
VW 06
VW 07
Chevy 02
Chevy 04
Ford 08
Ford 07
Ford 07

Now let's say that I wanted to only see the count of each by its variation. Like
There are
2 09 VW's
1 08 VW's
1 07 VW's
1 06 VW's
1 02 chevys
1 04 chevys
1 08 fords
2 07 fords


Is there some kind of Macro I could use to help me do this without having to use the filter to sort by one thing, and count out the other, than start with the next "Car manufacturer" and so on and so forth.

My superiors want charts made of that, and it is a lot more information than above, but the same effect. Any advice and input would be GREATLY appreciated, like you have no idea.

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « Excel Macro help » in :
Connect a database (MDB) to excel Show[VBA] Connecting a database (MDB) to excel Below is a tips of how to connect an Access database (MDB) in an application excel Add reference Microsoft DAO object librairy X.X In a general module (eg Module1) paste the code below...
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
[VBA: VB6] Using excel from another application Show[VBA: VB6] Using excel from another application Here is a little routine to call Excel from VB6 or another Office application. Paste in a general module (eg Module1) In VBA>> Insert>> Module and paste in the window ... In VB6>>...
Download Excel Macro Processor Show
Download DBF to Excel Converter ShowDescription The application is designed by WhiteTown Software. DBF to Excel is a tool that allows you to convert your file easily. Simple and easy to use, the application has been awarded from various places. Offering an intuitive interface, the...
Ask your question Reply

1

buster23, on Mar 28, 2009 7:28:30 am GMT
  • +1

Hi,
try this link to get tutorials about using excel macros:
http://www.helpwithpcs.com/tipsandtricks/microsoft-excel-mac­ro-tutorial.htm
this will help you.

   
Reply to buster23

2

 WutUp WutUp, on Apr 2, 2009 12:43:04 am BST
  • +1

This assumes that there is a heading in the first row, and your data you want to filter for unique records is in
column A. This uses the advanced filter to extract the unique records to column J. Then, it will copy and paste
values to column K and delete the "extract" from column J so the countif formula can be used. You said a
chart will need to be made from this information that is why the table will be created to the right of the data
within the same spreadsheet. Once the table is set-up, you can create a chart from there. You can change the
ranges (or columns) to suit your spreadsheet.

Hope this helps!


Sub CreateList()

Dim List
List = 2

Range("A2:A2000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J2"), Unique:=True

Columns("J").Select
Selection.Copy
Range("K1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Columns("J").ClearContents
Application.CutCopyMode = False

Do Until Range("K" & List) = ""

Range("L" & List) = "=countif(A2:A2000,K" & List & ")"

List = List + 1

Loop

Range("M2").Select

End Sub

   
Reply to WutUp WutUp
Ask your question Reply
They need your help