Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-16-2009, 02:48 AM
Ssendam Ssendam is offline Find and Replace - Variable change Windows XP Find and Replace - Variable change Office 2007
Novice
Find and Replace - Variable change
 
Join Date: Jul 2009
Posts: 1
Ssendam is on a distinguished road
Default Find and Replace - Variable change

Hi Dudes, I wonder if you can help ...

I want to do something involving find and replace and I'm not sure if it is possible, i.e.

I have a worksheet with a a number of links to other worksheets so, in cell H157 will me an addition link saying:



=+'\\Path\path\folder\[file A.xls]sheet name'!H157+'\\Path\path\folder\[file B.xls]sheet name'!H157

I want to add 'file C.xls' to the formulae and end up with:

=+'\\Path\path\folder\[file A.xls]sheet name'!H157+'\\Path\path\folder\[file B.xls]sheet name'!H157+'\\Path\path\folder\[file C.xls]sheet name'!H157

HOWEVER this formulae is repeated throughout the worksheet with only the cell reference changing. Thus I am looking to add the extra file addition on via a Find and Replace. I don't think I can do it because the cell reference will change in every case.

Is this possible to automate?
Reply With Quote
  #2  
Old 07-16-2009, 11:44 AM
Bird_FAT's Avatar
Bird_FAT Bird_FAT is offline Find and Replace - Variable change Find and Replace - Variable change Office 2007
Expert
 
Join Date: Apr 2009
Location: South East
Posts: 271
Bird_FAT is on a distinguished road
Default

You could make a macro!

Steps:
  1. Use a find string with wildcards for the variables (if the variable is a cell reference as in your example 'H157' all you need is a single asterisk '*' in place AND you need to ensure that you are searching the values NOT formulas)
  2. Click 'Find Next'
  3. If you need the variable i.e. 'H157', and it is at the end of the existing string, use [End > Shift+Left Arrow > Copy], if not modify as needed.
  4. End Button, to get to the end of the current reference string
  5. Type in the new addendum reference
  6. Paste your variable from step 3
If you then add a loop to the macro, it'll automate the process for you.

If you create the macro and paste the working code here, I'll help with the loop if you want.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace - Variable change Find and Replace is not working Brigitte Word 7 06-15-2021 05:10 PM
help with find and replace sjkiss Word 1 04-12-2010 08:08 AM
Find & Replace Insert Issue mipacker Word 0 02-18-2009 08:59 AM
Find and Replace Macro - A Better Way Tribos Word VBA 0 10-08-2008 03:22 AM
Find and Replace box flies off screen! M Skabialka Word 2 06-06-2006 10:05 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft