Excel Macro Query

scojones

It has been going on four years since I have done any VBA or excel macro coding so I said it might be best to ask this question here. I've tried googling but I didn't find any helpful information as of yet so I will continue googling while I await a response on here.

Basically, I have a column in Excel where five different strings occur, "Paper", "PDF", "OOO - PAPER", and a couple of others. What I want to do is count how many occurances of each of these there is in the column. I'm fairly sure I just iterate through the column and have a couple of if statements which will increase a counter for each string... for instance:

<while we are not at the end of the column>
if column.text == "Paper" then
   papercounter=papercounter+1

And so on. Is it possible that when this macro runs I can have it pop up with a message box with the results? I remember using MsgBox back in my VB days, can this be used inside an excel macro?

If you could just point me in the right direction I'd really appreciate it.
Cheers guys,
sj.

Evil Phil

Yep, you can use MsgBox in an excel macro, no probs. Also have a look at COUNTIF in the excel helpfile.

scojones

Cheers EvilPhil, got it sorted with the following:

[php]Sub Macro1()
'
' Macro1 Macro
' Macro recorded 15/08/2006 by localadmin
'
' Keyboard Shortcut: Ctrl+h
'

Sheets("Sheet1").Select
Worksheets("Sheet1").Range("A1:A200").Sort _
Worksheets("Sheet1").Range("A1")

'count the occurances

Dim papercounter As Integer
Dim pdfcounter As Integer
Dim ooocounter As Integer
Dim deliveryfailure As Integer

For Each c In Worksheets("Sheet1").Range("A1:A200").Cells
If c.Value = "PAPER" Then papercounter = papercounter + 1
If c.Value = "PDF" Then pdfcounter = pdfcounter + 1
If c.Value = "OOO - PAPER" Then ooocounter = ooocounter + 1
If c.Value = "Delivery Failure" Then deliveryfailure = deliveryfailure + 1

Next
MsgBox ("Num of Paper " & papercounter)
MsgBox ("Num of PDF " & pdfcounter)
MsgBox ("Num of OOO PAPER " & ooocounter)
MsgBox ("Num of Deivery Failure " & deliveryfailure)
End Sub
[/php]