VBA look and Replace

[Guest]

Hi,

Brand new to programming in VBA, I am looking for a code where the vba will
look or a particular words and replace in another column;
e.g.
Column A Column B
#Empty DEU
Portugal #Empty

this should read as
Column A Column B
Germany DEU
Portugal PTG

Your help is very much appreciated,

 

[Chip Pearson]

Try something like the following: Change the A1:A10 (column A only) to your
range of data.

Sub AAA()
Dim R As Range
For Each R In Range("A1:A10") '<<<< CHANGE RANGE
If R.Text <> vbNullString Then
If R(1, 2).Value = vbNullString Then
R(1, 2).Value = R.Text
Else
' do nothing
End If
Else
If R(1, 2).Value <> vbNullString Then
R.Value = R(1, 2).Text
Else
' do nothing
End If
End If
Next R
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Andy Pope]

Hi,

Here is a function,

Sub FindReplaceOffset(SearchIn As Range, SearchFor As String,
ReplaceWith As String, OffsetRows As Long, OffsetColumns As Long)
'
' Find all occurances of text in SearchFor and replace the cell
' defined by the offset rows and columns with the ReplaceWith text
'
Dim rngFind As Range
Dim strFirstAddress As String

With SearchIn
Set rngFind = .Find(SearchFor)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
If rngFind.Row + OffsetRows > 0 And _
rngFind.Row + OffsetRows <= Rows.Count Then

If rngFind.Column + OffsetColumns > 0 And _
rngFind.Column + OffsetColumns <= Columns.Count Then

rngFind.Offset(OffsetRows, OffsetColumns) = _
ReplaceWith

End If
End If
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And _
rngFind.Address <> strFirstAddress
End If
End With
End Sub

Sub x()

FindReplaceOffset ActiveSheet.UsedRange, "DEU", "Germany", 0, -1
FindReplaceOffset ActiveSheet.UsedRange, "Portugal", "PTG", 0, 1

End Sub

Cheers
Andy