Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Saturday, July 9, 2016 at 12:59:51 PM UTC+1, Claus Busch wrote:
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 I have 2 columns in Sheet2 (the Table ref in code) ColA is the originating values that I require to replace with what is in ColB The Data I require to replace in in SheetABC ColB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
Find/Replace Event or Find/Replace for Protected Sheet ... | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Replace method - cannot find any data to replace | Excel Programming |