Find specific text in a cell and update values in other cell

if you move from cell to cell and the search data keeps changing every time or for every row, i'm pretty sure there is nothing in the interface (eg - buttons you can click, formulas you can write) that will be able to handle this. and if you need to write code to do it, I don't think visual basic is good enough to make that simple. there are a things available that you can maybe do though. you can use static array and just throw the values in during the declaration section before the routine even starts running. you can also hard code everything during the looping process.

the other things you might be able to do, which just came to mind, is list all of your "key" data (e.g. - the date you want to search by, for each row) in a separate sheet, line by line, and then use code to loop through that sheet, line by line, capture the key data used for the search criteria and ask the code to look at the corresponding row on the main sheet to find the data you want. then obviously, update the data you're referring to.

per my second though, there also might be an internal function in excel you can use to look up data and pick out what you need. but I'm 50% confident that your requirements are too complex for excel's resources to handle it.

Thank you so much. This really works.

please mark this as solved then.

Hi nankw83,

One more question around the same problem.
I'm able to get the results in Col. V basis Col. C observation. The non-matching results show as N/A.
Now, I'm trying to do: all the N/A entries in Col. V should go through the same code again but this time in Col. D to get the results.
I'm trying to use Do While loop but it seems to be not working. Any help please. Here is my code I'm writing in a new module:

HTML Code: 

Hi ... Just to clarify, the code now reads column D then loop through each row to see a matching then paste the output in column V. What you want to do is once this process is completed, repeat the process but instead of column D as a source take column C as source & only consider entries with N/A in column V & then update the results in column V again ?

I'm a bit confused between column D & C as the code reads column D & you said column C

Sorry for the confusion. But it is vice versa.
My code was earlier taking Col. C as source. I now want :

Once the process is completed (with Col. C), repeat the process but instead of column C as a source take column D as source & only consider entries with N/A in column V & then update the results in column V again.

The code which I've shared above is what I'm trying to write to meet this requirement.

Hey, this works

Could you please make me understand few things here for my knowledge:

a. a = ws.Range("C2", ws.Range("C" & Rows.Count).End(3)).Resize(, 2)- Why have we used End(3)? I usually use xlUp, xlDown etc.. and what is Resize (,2) going to do?

b.

HTML Code: 

Please help me understand this part.

Wow.. It is so surprising to see and learn the different ways of doing the same work. Sure, let me try on few records and understand it . Once again, thank you for your assistance

Hi nankw83,

Sorry to bother you again on the same code. While this code works great in most of the row items, there are still some rows where it is not giving the results. Issue is:
We have matching keywords in Col.D but code gives us results as N/A. Below are few examples where the results shouldn't be N/A

INC001056764153 - WAAS
INC001056770946 - Infoblox
INC001056547757 - Bandwidth
INC001056565241 - ITRMS
INC001056412829 - wireless

I have also attached the macro file for your reference. Please assist.

Okies, let me try Thank you