#1
|
|||
|
|||
Macro to search names/ids and replace text in another column
Hi guys,
I have got a list of names and ids (actually i have a large excel file and in sheet 3 ("lfd. & geschlossene Vergleiche") in one column there is the id and in another column therre is the name). And I have got a second excel file that is even larger and in this file there is also a column with the id and one column with the name. Okay the name is actually irrelevant, lets only care about the ids... What i want to do now is the following: Search all the ids (the id is in column B) of the first excel file (beginning in row 112 until the end) one after another in the second file (here the id is in column A) and then change the text in the matching row of the second excel file: - column H to "closed" - column AP to "date [line break:Alt+010] settlement reached [line break] amount paid" - column AZ to "closed: settlement" The date is found in column AN of the first excel file (sheet 3: "lfd. & geschlossene Vergleiche"). The amount paid is found in column AH of the first excel file (sheet 3: "lfd. & geschlossene Vergleiche"). For example: Find the id of column B, row 112 of the first file in column A of the second file and replace the text in this row as described above. Then find the id of column B, row 113 of the first file in column A of the second file and so on. Since I have to do this every week it would be great to find a macro that can do this annoying work for me... Really thank you all very much in advanced for your competent help!! <3 |
#2
|
|||
|
|||
Put this in a Module for the master workbook. Run macro with slave workbook open. Change SlaveIDs.xlsm to your slave workbooks name. Test on backup copies.
Code:
Sub Main() Dim s1 As Worksheet, s2 As Worksheet Dim r1 As Range, r2 As Range, f2 As Range Dim r1e As Range, r2e As Range, c As Range Set s1 = ThisWorkbook.Worksheets("lfd. & geschlossene Vergleiche") 'Assumes slaveids.xlsm is open already and first sheet is sheet to modify. Set s2 = Workbooks("SlaveIDs.xlsm").Worksheets(1) Set r1e = s1.Cells(Rows.Count, "B").End(xlUp) Set r1 = s1.Range("B112", r1e) Set r2e = s2.Cells(Rows.Count, "A").End(xlUp) Set r2 = s2.Range("A112", r2e) For Each c In r1.Cells Set f2 = r2.Find(c.Value, r2e, xlValues, xlWhole, , xlNext, , True) If f2 Is Nothing Then GoTo NextC s2.Cells(f2.Row, "AP").Value = s1.Cells(c.Row, "AN").Value & vbLf & _ "settlement not reached " & vbLf & s1.Cells(c.Row, "AH").Value f2.Cells(f2.Row, "AZ").Value = "closed: settlement" NextC: Next c End Sub |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding terms to a search & replace macro | Ulodesk | Word VBA | 6 | 03-29-2018 05:30 AM |
Macro on Search and Replace | davidhuy | Word VBA | 1 | 12-19-2014 04:47 AM |
Macro to search warning text style and replace the text color | rohanrohith | Word VBA | 3 | 11-27-2014 01:08 PM |
Macro help - search for value, paste a value in another column | IRollman | Excel Programming | 1 | 01-14-2014 01:05 PM |
matching names in column A with names in column C | bob999999 | Excel | 1 | 04-28-2013 11:21 AM |