Advanced Find and Replace in Excel

[Brockers]

My (training) client needs a way of being able to use Sheet2 of a Workbook to update values in a List stored in Sheet1. It's my birthday and I don't need this today!!!!

The breakdown is:

Make a Text Entry in Sheet2:A2 to search for, Search Sheet1:Col G for exact matches of the (Sheet2:A2) entry and then replace the adjacent cells in Sheet1:Col H with an Entry made in Sheet2:B2.

E.G.

Go and look for all the Engineers (Search Text) in Col G and Change their Pay Rate in Col H to a New Rate (Replace Text), it seems they are just to lazy to use Find And replace really!!!!

I have toyed with VlookUps but it effectively needs to Lookup then move across a Col then Replace! Find and Replace All would do if I could easily reference the cells! I am very comfortable with VBA in Access just not as much in Excel!

It's the kind of thing that an Update Query in Access would do in Seconds but my client won't stray from Excel!!!!

Regards

Brockers
Instructor
New Horizons Manchester England

 

[xlbo]

no way but VBA to update cells that formulae don't reside in

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Brockers]

I have made a solution to my problem by using an update query in Access that references a linked table coming from the Excel file!

I've added a nice form and some error handling and am currently looking at training Monkeys to use it, it's that simple!

It works a treat, just hope the client is happy!

Regards

Brockers
Instructor
New Horizons Manchester England