Search for criteria and count
 

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!

Search for criteria and count
 

Hi
why not use the worksheet functions
SUMIF/COUNTIF

--
Regards
Frank Kabel
Frankfurt, Germany

"Tempy" schrieb im Newsbeitrag
...
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!

Search for criteria and count
 

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

Les Stout

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

Search for criteria and count
 

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.

--
Regards
Frank Kabel
Frankfurt, Germany

"Tempy" schrieb im Newsbeitrag
...
Hi Frank,
yes i can, but not sure how to do it with VB?

Les Stout

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

Search for criteria and count
 

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

--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
Hi Frank,
yes i can, but not sure how to do it with VB?

Les Stout

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

Search for criteria and count
 

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!

Search for criteria and count
 

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!

Search for criteria and count
 

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.

--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
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!

Search for criteria and count
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Tempy" schrieb im Newsbeitrag
...
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!

Search for criteria and count
 

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

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Tempy" wrote in message
...
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!

Search for criteria and count
 

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

Thank you

Les Stout

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