Search for criteria and count

T

Tempy

Good day, i am not a programmer but a dabbler and need some code to do
the following:

In sheet2 i have a list codes with thier discription e.g. VA71, VA72
UB73 etc....

On sheet1 i have a list of codes only in column A

What i need to do is, loop down the list in sheet2 and with each code,
find how many times this code appears in sheet1.

Once i have the quantity i must past it to sheet2

Any help is appreciated.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
F

Frank Kabel

Hi
first question: why VBA (it will be slower, create more overhead,
etc.). Of course you could use something like
application.worksheetfunction.sumif(...)
within VBA.
 
T

Tempy

Hi Frank,

this must be done on a daily basis and there are approx.80 different
codes that have to be looked up.

As i said before, i am very new to this game and not sure how to start
with the code.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tempy

Hi Tom,

I know what he is saying, but in my last message i also said that there
are about 80 different codes and this must be done on a daily basis ?

So ithought it would be quicker and easer with VB

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
T

Tom Ogilvy

assume the first code in the list of codes is in Sheet2, in cell A1

in B1 put in the formula

=countif(Sheet1!$A:$A,A1)

then drag fill this down next to the 80 cells of codes.

This will give you your counts.
 
T

Tom Ogilvy

Assume 80 codes on Sheet2 in column A starting in A1

Sub Macro1()
Dim rng as Range
With ThisWorkbook.Worksheets("Sheet2")
set rng = .Range(.cells(1,1),.cells(1,1).End(xldown))
End With
rng.offset(0,1).Formula = "=countif(Sheet1!$A:$A,A1)"
End Sub

Call it from the workbook_Open event

in the ThisWorkbook module

Private Sub Workbook_Open()
Macro1
End Sub
 
Top