 # Calculate If Specific Word Found In Column

• Hi,

I would like to use the following code to see if the word "reservoir" shows up in any of the rows in a certain column. If it does I want to insert the formula =SUM(D7:D257)*0.1 into cell C2 and if it doesn't then I want to put a 0 into cell C2. I've tried many routes but can't get past the error if it can't find the word.

Code
``Range("B7:B257").Find(What:="Reservoir").Activate``

Thanks

• Re: Find Word In Column, Set Formula Based On Response

No need for code, just enter the following formula in C2.
[bfn]=IF(COUNTIF(B:B,"Resevoir")>0,SUM(D7:D257)*0.1,0)[/bfn]

• Re: Find Word In Column, Set Formula Based On Response

TURTLE,

Thanks for the quick reply, and the formula would work great if the cell only contained "Reservoir" but here's an example of what one row would look like containing reservoir

"Reservoir,50 Gal,JIC Type Style A,36 Long X 24 Wide X 23 Tall"

I have to be able to search for "Reservoir" that is inside otehr text in multiple rows.
When I go to edit - Find it works perfect in finding "Reservoir" but when its not there I get errors.

Thanks

• Re: Find Word In Column, Set Formula Based On Response

[bfn]=IF( COUNTIF(B:B,"*Resevoir*")>0,SUM(D7:D257)*0.1,0)[/bfn]

• Re: Find Word In Column, Set Formula Based On Response

That's amazing! . . . works perfect

=IF( COUNTIF(B:B,"*Reservoir*")>0,SUM(D7:D257)*0.1,0)

Thanks

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!