Replace text if a cell is populated via vba

[excel_2009]

Hi All,

Would it be possible (via VBA) to replace text in an already existing column IF a cell is not blank, the existing text is as follows (in one column on worksheet one):

[TABLE="width: 108"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name:Clark Kent[/TD]
[/TR]
[TR]
[TD]Address:Smallville[/TD]
[/TR]
[TR]
[TD]State:Kansas[/TD]
[/TR]
[TR]
[TD]Country:US[/TD]
[/TR]
</tbody>[/TABLE]

on worksheet two the following fields are visible:

[TABLE="width: 108"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name:Tony S[/TD]
[/TR]
[TR]
[TD]Address:[/TD]
[/TR]
[TR]
[TD]State:[/TD]
[/TR]
[TR]
[TD]Country:DE[/TD]
[/TR]
</tbody>[/TABLE]

So the plan would be for the code to replace Clark Kent with Tony S and replace US with DE and leave the Address, State as is.

Would this be possible, if so how?

Many thanks!!

 

[kweaver]

Something tells me there's more to this than a possible 4-cell replacement.

Sub Replacements()
If Sheets("Sheet2").Range("B1") <> "" Then
  Sheets("Sheet1").Range("B1") = Sheets("Sheet2").Range("B1").Value
  ElseIf Sheets("Sheet2").Range("B2") <> "" Then
  Sheets("Sheet1").Range("B2") = Sheets("Sheet2").Range("B2").Value
  ElseIf Sheets("Sheet2").Range("B3") <> "" Then
  Sheets("Sheet1").Range("B3") = Sheets("Sheet2").Range("B3").Value
  ElseIf Sheets("Sheet2").Range("B4") <> "" Then
  Sheets("Sheet1").Range("B4") = Sheets("Sheet2").Range("B4").Value
End If
End Sub