Trying to count unique values in a column (10%) for use later in macro

[tjroby]

I'm new here and kindof self-taught at writing macros so I need help!

I'm working on a random audit of prices on an invoice and I need to pull 10% of the unique item numbers aside as a random sample. I've got a macro that will pull 10 items and their prices to a seperate sheet, but I'd like to replace the 10 with 10% of the total unique item numbers on the invoice. Each invoice will have a different number of unique item numbers on it.

I thought I could use a simple counta function in my macro but I'm getting an "invalid qualifier" error on the .CountA I'm attaching a spreadsheet with just the part of my macro I'm working on for the 10% calculation. "nItem" will be the number of random samples to be taken from the total list.

I have another issue with the results including duplicates but I'm taking these issues one at a time.

Any ideas?

[p24leclerc]

here is how your line of code should look like to work properly:

    nItem = (Application.WorksheetFunction.CountA(Range("B2", Range("B65536").End(xlUp)))) * 0.1

Also, put this macro in a module instead of being in Sheet1 in VB editor. Otherwise, you won't be able to write your answer "nitem" in Sheet2

[tjroby]

Thank you p24leclerc! That works. Now I will work it into my full macro for this project. And, the only part I have left to fix is getting duplicates. I'll see if I can put it together and post on here for some help on that part too.

I've posted my follow-on question:

http://www.excelforum.com/excel-prog...html?p=3111650