Five Digit Append

[Evremonde]

Looking for some VBA code to help with the following need. Rows in column G contains a five digit number. Rows in column H contains a combination of text and numbers. Combinations in column H always ends with “-XXXXX”. I need to append the five digit number in column G such that the five digit number replaces the “XXXXX” in column H. Additionally, if the cell is blank in Column G then do nothing to the corresponding row contents of column H. Can you help?

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub ReplaceXXXXX()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "H").End(xlUp).Row
  Range("H1:H" & LastRow) = Evaluate(Replace("IF(H1:H#="""","""",SUBSTITUTE(H1:H#,""XXXXX"",G1:G#))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[Trebor76]

Deleted my attempt as Rick's solution is much better.

 

[Evremonde]

This solution works. But now I see a flaw my original request. Suppose a number column G changes. Can this solution be made to update the corresponding row in column H in the code is run again (or multiple times)? Thank you for the help...

 

[Rick Rothstein]

This solution works. But now I see a flaw my original request. Suppose a number column G changes. Can this solution be made to update the corresponding row in column H in the code is run again (or multiple times)? Thank you for the help...

Give this macro a try then...

[table="width: 500"]
[tr]
	[td]Sub ReplaceLast5Characters()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "H").End(xlUp).Row
  Range("H1:H" & LastRow) = Evaluate(Replace("IF(H1:H#="""","""",LEFT(H1:H#,LEN(H1:H#)-5)&G1:G#)", "#", LastRow))
End Sub[/td]
[/tr]
[/table]