Search and Replace Text strings based on Column Header

[Pete2020]

I have 2 columns data in

Sheet 1 -Column A is a Emp ID and Column B is a text with identified text Keywords to be replaced.
Sheet 2- Column A has Emp ID which is a primary key to sheet 1 to compare

Once the column A ID is matched in both sheets, then Column B to Column K based on header name(keyword to search in column B), the respective keywords in each cell are to be replaced.

I have around 10k data Looking for a Macro or a VB function to search and replace.

I am very thankful for creating this wonderful platform and helping us

 

[Rick Rothstein]

Are your Employee ID numbers aligned in your actual data as shown in your attachment? That is, for any single Employee ID in Column A, will its counterpart always be found on the same row in Column E?

 

[Pete2020]

The Column E and adjacent columns data is presented in Sheet2 column A.

The EMP ID is presented in Column A of sheet 2. Its just for screen shot purpose i mentioned at Column E.

 

[Pete2020]

Reproducing the data in XL2BB format

I have 2 sheets in

Sheet 1 -Column A is a Emp ID and Column B is a text with identified text Keywords to be replaced.
Sheet 2- Column A has Emp ID which is a primary key to sheet 1 to compare

Once the column A EMP ID is matched in both sheets, then Column B to Column K based on header name(keyword to search in column B), the respective keywords in each cell are to be replaced.

I have around 10k data Looking for a Macro or a VB function to search and replace.

For reference I mentioned

Seach and replacemrexcel.xlsx
	A	B	C
1	EMP ID	Before Macro	After Macro - result in Column C
2	1101	I am {name} and currently working as {designation} and working at {Company}	I am John Peter and currently working as Senior Human Resources Recruiter and working at Nokiasys
3	1102	I am {name} and currently working as {designation} and working at {Company}	I am Jamuna and currently working as Assistant Human Resources Manager and working at KGD Architecture
4	1103	I am {name} and currently working as {designation} and working at {Company}	Based on Column A Sheet 2 and sheet 1 Match - the respective keywords to be replaced.
5	1104	I am {name} and currently working as {designation} and working at {Company}	Based on Sheet 2 and sheet 1 Column A Match - the respective keywords to be replaced.
6	1105	I am {name} and currently working as {designation} and working at {Company}	Based on Sheet 2 and sheet 1 Column A Match - the respective keywords to be replaced.
7	1106	I am {name} and currently working as {designation} and working at {Company}	Based on Sheet 2 and sheet 1 Column A Match - the respective keywords to be replaced.
8	1107	I am {name} and currently working as {designation} and working at {Company}	Based on Sheet 2 and sheet 1 Column A Match - the respective keywords to be replaced.
Sheet1

Sheet 2 Column A (Reference to replace the data)

Seach and replacemrexcel.xlsx
	A	B	C	D
1	EMP ID	{name}	{Designation}	{company}
2	1101	John Peter	Senior Human Resources Recruiter	Nokiasys
3	1102	Jamuna	Assistant Human Resources Manager	Human Resource
4	1103	Shakambari Nayak	HR Recruiter	Roland & Associates
5	1104	Divakar Nayak	Human Resources Executive	IDS Software Pvt Ltd
6	1105	Veena J	Senior HR Generalist	KGD Architecture
7	1106	Kruthi Or Rmaya	HR Executives	Ish Infotech Private Limited
8	1107	Monica	HR Manager	Consulace Business Solutions
9	1108	abhay punjabi	Human Resources Recruiter	Vertisystem
10	1109	Ravi	HR Partner	Udaya Enterprises
11	1110	Shubhra Tripathy	Senior HR Associate	Adodis Technologies Pvt. Ltd.
Sheet2

 

[Peter_SSs]

Even though you have pointed out these tables are actually on different sheets, you haven't addressed the real point of Rick's question.
For example, in what you have posted here in post #5, EMP ID 1105 appears on worksheet row 6 in both Sheet1 and Sheet2. It is the same situation for each of the other sample EMP IDs. Rick's question is: Does that actually happen for all EMP IDs in your real data?

 

[Pete2020]

Thanks Peter.You are Back.

The Answer is No for Rick Question. The data will not be in same row. It can be anywhere in the column A of sheet1

 

[Peter_SSs]

The Answer is No for Rick Question.

OK, thanks. Give this a try with a copy of your data.

Sub InsertDetails()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, r As Long, ubb2 As Long
  Dim s As String
  
  b = Sheets("Sheet2").Range("A1").CurrentRegion.Value
  ubb2 = UBound(b, 2)
  Set d = CreateObject("Scripting.Dictionary")
  For i = 2 To UBound(b)
    d(b(i, 1)) = i
  Next i
  With Sheets("Sheet1")
    a = .Range("A2", .Range("B" & .Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      r = d(a(i, 1))
      s = a(i, 2)
      For j = 2 To ubb2
        s = Replace(s, b(1, j), b(r, j), 1, -1, 1)
      Next j
      a(i, 2) = s
    Next i
    .Range("C2").Resize(UBound(a)).Value = Application.Index(a, 0, 2)
  End With
End Sub

 

[Pete2020]

Debug Issue

 

[Peter_SSs]

What is the full actual error message?

If you Debug and hover over j and then r in that yellow line, what values show in the pop-up?

Also hover over i somewhere and report that value too please.

Here is my result using your sample data (after mixing up the rows in Sheet2 so they didn't all match).

Pete2020 2020-05-07 1.xlsm
	A	B	C
1	EMP ID	Before Macro
2	1101	I am {name} and currently working as {designation} and working at {Company}	I am John Peter and currently working as Senior Human Resources Recruiter and working at Nokiasys
3	1102	I am {name} and currently working as {designation} and working at {Company}	I am Jamuna and currently working as Assistant Human Resources Manager and working at Human Resource
4	1103	I am {name} and currently working as {designation} and working at {Company}	I am Shakambari Nayak and currently working as HR Recruiter and working at Roland & Associates
5	1104	I am {name} and currently working as {designation} and working at {Company}	I am Divakar Nayak and currently working as Human Resources Executive and working at IDS Software Pvt Ltd
6	1105	I am {name} and currently working as {designation} and working at {Company}	I am Veena J and currently working as Senior HR Generalist and working at KGD Architecture
7	1106	I am {name} and currently working as {designation} and working at {Company}	I am Kruthi Or Rmaya and currently working as HR Executives and working at Ish Infotech Private Limited
8	1107	I am {name} and currently working as {designation} and working at {Company}	I am Monica and currently working as HR Manager and working at Consulace Business Solutions
Sheet1

 

[Peter_SSs]

My guess is that you have an EMP ID on Sheet1 that does not exist on Sheet2