Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default Find and Replace Q

I took the code below from SpreedsheetGuru, but want to tweak it for my specific needs and I am a little lost

Code below Finds & Replaces from a Table values in every worksheet in the workbook except where the Table resides, how can I tweak this so that it only replaces Data it finds in sheet "Sheet ABC" Column B?

Sub Multi_FindReplace_SubACC()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to your table
Set tbl = Worksheets("Sheet5").ListObjects("Table1")

'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)

'Designate Columns for Find/Replace data
fndList = 1
rplcList = 2

'Loop through each item in Array lists
For x = LBound(myArray, 1) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
For Each sht In ActiveWorkbook.Worksheets
If sht.Name < tbl.Parent.Name Then

sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

End If
Next sht
Next x

End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Find and Replace Q

Hi,

Am Fri, 8 Jul 2016 23:33:33 -0700 (PDT) schrieb :

I took the code below from SpreedsheetGuru, but want to tweak it for my specific needs and I am a little lost


what are you searching for and what is the replacement?


Regards
Claus B.
--
Windows10
Office 2016
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and Replace - Replace with Blank Space Studebaker Excel Discussion (Misc queries) 4 April 3rd 23 10:55 AM
Find/Replace Event or Find/Replace for Protected Sheet ... Joe HM Excel Programming 2 October 27th 07 03:55 PM
find and replace - replace data in rows to separated by commas msdker Excel Worksheet Functions 1 April 15th 06 01:00 AM
Using Find and Replace to replace " in a macro snail30152 Excel Programming 1 April 13th 06 11:58 PM
Replace method - cannot find any data to replace Mike Excel Programming 5 April 6th 06 08:56 PM


All times are GMT +1. The time now is 08:13 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"