Search for a String and insert Value in another cell

I have a big Excel file (over 8000 rows) with repeating data. Instead of adding information by hand I feel some VBA coding would come in handy .

I need help wring a macro that Searches for a particular string in the 2nd column. When it finds it, it inserts a value in a cell 3 columns to the right on the same row as the found string. This should continue until there are no more strings matching the search criteria.

Thanks.

Originally Posted by TMShucks

What string? What value? Is it a one off or are you going to need to read a source list or input the value(s)?

A sample workbook would best demonstrate your requirement(s).

Regards, TMS

Thanks for the response. Both the String and the value will just be input.

For example, the String 'Supply and fix superflush ceramic cistern together with flush' appears about 200 times in the WorkSheet (in the B-column). I would want to search for that string, then, when it is found, insert a value '=300000*1.45' in the same row but column-E for all the 200 instances of that String.

I have attached a zipped Excel File to illustrate what I mean.

Regards,

Mandy

You could also just apply a filter. In the home tab under filter click on sort and filter and then filter. Then under description you can filter out all other descriptions except for the one you want. Then you could put whatever number you want under rate and it will only apply to the ones you have filtered. You can then do this with all of the other rates that you have.

Also, check out this thread as this guy had a very similar problem:
http://www.excelforum.com/excel-prog...ying-data.html

Originally Posted by erok

You could also just apply a filter. In the home tab under filter click on sort and filter and then filter. Then under description you can filter out all other descriptions except for the one you want. Then you could put whatever number you want under rate and it will only apply to the ones you have filtered. You can then do this with all of the other rates that you have.

Also, check out this thread as this guy had a very similar problem:
http://www.excelforum.com/excel-prog...ying-data.html

Thanks a lot erok, the application of a filter actually worked very well! This really makes my life easier!

Originally Posted by TMShucks

There is probably more than one way to attach this. Before I put a lot of effort into providing a solution which matches your stated requirements, have you considered putting together a "master" table of task descriptions and associated costs? You could then use VLOOKUP in column E to pick up the values.

I think that would be simpler than running a macro several times and inputting the descriptions and values ... and you'd have a permanent record of what values you have used.

I suspect that it may be more complex than that but I'll hang fire until you've had time to think about it and reply.

Regards, TMS

Thanks a lot for your help. I did some bit of digging and I arrived at the following:

Please Login or Register  to view this content.

The Code works ok, but the problem is the part in Red. As I am assigning =E11 to

Please Login or Register  to view this content.

(including cell E11 itself), it means I'll lose the value of cell E11, so how can I rewrite the line in red and the one above it to mean "for all the cells except cell E11, do this" ? In other words, I'd like to assign =E11 to all but cell E11 itself!

Thanks once again.