Macro query - finding mutliple occurences of text in a column

[Mcneilius]

Hi all

I have a list of various words in a column and a correspondin
numerical value for it in an adjacent column.

I would like a macro that "scans" down the column of words and adds th
corresponding numerical values for a particular word (see attache
file). For example in the attached file it could look for the wor
"petrol" and add 20 and 15 to return 35.

I've had a stab at a macro involving "find", "match", "offset" etc wit
no joy... I wondered if anyone would be able to post suitable cod
suggestions?

Eventually I'd like for the search word (eg. "petrol") to be specifie
in a list and the macro to search for all words in a list - is thi
possible?

Any help would be much appreciated. Apologies that I posted a simila
thread - I don't think my query was very clear. I'm very keen t
automate this in a macro and steer clear of pivot tables!

Many thanks for your time.

Nei

Attachment filename: neilmacro.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66550

 

[Tom Ogilvy]

dim rng as Range
set rng = Range(cells(1,1),Cells(1,1).End(xldown))
msgbox Application.Sumif(rng,"petrol",rng.offset(0,1))

I didn't look at your file, but you are describing the sumif
worksheetfunction. You can use it as shown.

as written, it assumes the words are in column A, starting in A1 and the
corresponding numbers in Column B

 

[Mcneilius]

Many thanks for that Tom, works a treat!

I'm fairly new to macros, but I should be able to read up on Sumif an
modify the code accordingly, if for example, the range doesn't start i
A1, or I need the search word (eg. "petrol") to be be specified in
cell, shouldn't I?

Thanks again for your help, very much appreciated.

Kind regards

Nei