Search for criteria and count

[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

 

[Frank Kabel]

Hi
why not use the worksheet functions
SUMIF/COUNTIF

 

[Tempy]

Hi Frank,
yes i can, but not sure how to do it with VB?

Les Stout

 

[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.

 

[Tom Ogilvy]

He meant don't do it with VB, use the Worksheetfunctions to get you counts.

 

[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

 

[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

 

[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.

 

[Frank Kabel]

Hi
as Tom said: use a formula and copy it
He posted one example for this

 

[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

 

[Tempy]

Sorry, that took a while !!
Blonde moment!!

Thank you

Les Stout