find and give value in next cell - Need Simple Formula

[VBABEGINER]

Hi Experts,

I want below code in formula.. Pls assist..

tpa = is a word available in cell

wherever i will find tpa, next cell of that should be return 1 or 0

Dim myRange As Range
Dim myCell As Range
rng = Range("B" & Rows.Count).End(xlUp).Row
Set myRange = Range("B2:B" & rng)




'example=  ABC tpa PQR


For Each myCell In myRange
    If myCell Like "*tpa*" Then 
        
    end if
next

 

[Eric W]

Try Offset:

x = myCell.Offset(,1)

 

[VBABEGINER]

Try Offset:

x = myCell.Offset(,1)

Hi Eric, Thanks for reply.. But i didnt get what you tried to say. The code which i posted that too written by me, i got from mrexcel only..

Pls give me formula instead.. Thanks

 

[Eric W]

I gave you a VBA answer because you posted VBA code and apparently wanted it updated. As it is, that code doesn't do anything. If you want a worksheet formula, that's possible, but you need to be specific as to what you want. It appears you want to search a range, look for "*tpa*" and if found, return the value to the right of it. If so, try

=SUMIF(A1:A5,"*tpa*",B1:B5)

But what do you want to do in case there are multiple matches? It might help if you could provide examples of what you want.

 

[VBABEGINER]

I gave you a.....
But what do you want to do in case there are multiple matches? It might help if you could provide examples of what you want.

Hi Eric, Thank you for your valuable reply and time...

Yes, i do have 65,000 data in col A. In certain cell's there is "tpa" word either single or placed with other text too..

I wanted if, *tpa* present in Col A cell then it should return 1 in cell of Col B else, return 0.

Hope this is description im help you understand..

 

[VBABEGINER]

i found it like some thing this -

=IF(IFERROR(SEARCH("tpa",A1,1),0)=0,"No","Yes")

 

[VBABEGINER]

hI ERIC, DO YOU HAVE ANYTHING SMARTER WAY PLS..

 

[Eric W]

That should work. You can remove the =0 from the formula if you want. Thanks for the update. Glad you found something that works for you.

 

[VBABEGINER]

Thanks Eric, all your time and effort..