VBA in Microsoft Excel: Find & Replace method macro across multiple files
 

I have two files and three columns of data. In Workbook1-sheet1 I want
to copy the value (x) of column B and then search this specific value
in a Range of column A in Workbook2 (imported text file to a new Excel
Worksheet). When I find that value, I want to change the data found in
the row of the substring (started as long 60.ª position with 8
character length) but with column BE to a different value (y) of sheet1
in Workbook1. For example, when I find the number 13090011 (Excel
Workbook1 - "C130508a.SAP") anywhere in the Range of column A (Excel
Workbook2 - "C130508a.SAP.xls"), I want to substitute the substring
character data in cell found with the value "13090017".
The recorded Macro code is below :

Sub Macro1()
'
' Macro1 Macro
' Macro gravada em 07-09-2005 por Administrador
'
' Atalho por teclado: Ctrl+o
'
Range("B1").Select
Selection.Copy
Windows("C130508a.SAP").Activate
Cells.Find(What:="13090011", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Windows("C130508.SAP.xls").Activate
Range("BE1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("C130508a.SAP").Activate
ActiveCell.Replace What:="13090011", Replacement:="13090017",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Cells.Find(What:="13090011", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Windows("C130508.SAP.xls").Activate
End Sub

I need help!, because i want this proceedure to be in a loop clause!
The values to change in first Workbook are variables and macro is only
sucessful using values like constants. The second Workbook to change is
a txt file.

Thanks in advance!

Best regards,

Pedro123


--
Pedro123
------------------------------------------------------------------------
Pedro123's Profile: http://www.excelforum.com/member.php...o&userid=27052
View this thread: http://www.excelforum.com/showthread...hreadid=465485