Microsoft Office Forums Macro to search names/ids and replace text in another column

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-03-2018, 03:32 AM
Kalü Kalü is offline Macro to search names/ids and replace text in another column Windows 10 Macro to search names/ids and replace text in another column Office 2010 32bit
Advanced Beginner
Macro to search names/ids and replace text in another column
 
Join Date: Apr 2018
Posts: 41
Kalü is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-06-2018, 07:14 PM
Kenneth Hobson Kenneth Hobson is offline Macro to search names/ids and replace text in another column Windows 10 Macro to search names/ids and replace text in another column Office 2016
Novice
 
Join Date: Jun 2018
Posts: 27
Kenneth Hobson is on a distinguished road
Default

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
Reply With Quote
Reply

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 names/ids and replace text in another column Macro to search warning text style and replace the text color rohanrohith Word VBA 3 11-27-2014 01:08 PM
Macro to search names/ids and replace text in another column Macro help - search for value, paste a value in another column IRollman Excel Programming 1 01-14-2014 01:05 PM
Macro to search names/ids and replace text in another column matching names in column A with names in column C bob999999 Excel 1 04-28-2013 11:21 AM


All times are GMT -7. The time now is 11:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft