Find and Replace Data from one into another

[inetuse]

Hello

I have two excel file id.xls and amount.xls

ID.xls format

ID Amount
12345 100
54321 200
11111 250
33333 500

Amount.xls format
Column A Amount

Total 12345

Total 11111

Total 33333


I want the macro to find the ID from id.xls and replace the amount from
ID into amount.xls, and if it couldn't find the id in amount.xls it has
to skip and find the next id till the eof in id.xls

Thanks for any suggestion.

 

[Jane Pratt [MSFT]]

Hi

Unfortunately I am not completely clear on how you are laying out the
information in the Amount spreadsheet, so I have assumed that column A
contains something like "Total 11111" and the total will be inserted in to
Column B. Here is a small sample that demonstrates this (it assumes that
the workbooks are open etc):
Sub Replace()
Dim IDRange As Range
Dim AmountRange As Range
Dim cell As Range

'Identify range containing ID's
Set IDRange = Workbooks("Id.xls").Sheets("Sheet1").Range("A1")
Set IDRange = Range(IDRange, IDRange.End(xlDown))

'Identify range containing Summary Data
Set AmountRange = Workbooks("Amount.xks").Sheets("Sheet1").Range("A1")
Set AmountRange = Range(AmountRange, AmountRange.End(xlDown))

'Loop through
For Each cell In IDRange
InsertAmount AmountRange, cell
Next
End Sub

Sub InsertAmount(RangeToSearch As Range, IDRange As Range)
Dim MatchRange As Range
Dim strid As String
On Error GoTo done

'Get text of Id
strid = Trim(Str(IDRange))

'Search for range containing the Id
Set MatchRange = RangeToSearch.Find(strid)

'Put value to the right of the id in the cell to the right of the
matching range
MatchRange.Offset(0, 1).Value = IDRange.Offset(0, 1).Value

done:
End Sub

Hope this helps!

Regards
Jane Pratt
Developer Tools Support
Microsoft UK

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.