macro question and count question... thanks!

**bstaff** · 02-06-2007, 03:54 PM

Thanks for looking.

Question 1:
I have a spreadsheet that I use the auto format function on. When I search one of the columns that has text to display only records that are "complete" for example, those records are displayed, but the row numbers jump around and I need to figure out the easiest way to count the number of records with a certain status.

Currently, I am inserting a column and putting a 1 next to each record and using the sum function.

Is there an easier way?

Question 2:
I have an access DB that generates a spreadsheet for me and I would like to have a macro available that does some simple formatting for me.

I have figured out how to make a macro, but it only seems to work when I am using the file I created the macro in.

When my DB generates the next excel report, the macro is no longer available.

Any thoughts?

Thanks very much for your time.

**Paul** · 02-06-2007, 04:02 PM

Regarding counting the number of occurrences of a value in a column, you should be able to use the COUNTIF(range,criteria) function. For example:

A1 through A5 contain: Complete, No, No, Complete, Complete
A6 has the formula: =COUNTIF(A1:A5,"Complete")
Result in A6 should be: 3

------------------------------------------

Regarding question 2, if you save the Macro in your personal macro folder, it should be available at any time. When creating a macro you can specify it's name, shortcut key, storage location ("Store macro in:") and description. For the 'Store Macro In' drop-down box, choose 'Personal Macro Workbook'. You should then be able to go into any Excel workbook and choose Tools>Macros... and see your macro listed there.

**MDubbelboer** · 02-06-2007, 04:02 PM

question 1: =countif(range,criteria)
i.e. countif(A1:A100,1) will count the occurences of 1 in A1 to A100

question 2: try adding the macro to your personal.xls
This is your personal collection of macros that is automatically opened and running with excel. When recording a macro choose "Store Macro in:" and choose Personal.xls

hth

*EDIT* pjoaquin beat me by a couple seconds

**VBA Noob** · 02-06-2007, 04:07 PM

Hi,

Try a countif function e.g

=COUNTIF(B:IV,"a")

This counts all the letter a's from Col B to IV

You could add the macro to your personal macro's.
When you recorded the macro before you tick ok select personal macro workbook from the drop down

VBA Noob