Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA look and Replace
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA look and Replace
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) "Vitordf" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA look and Replace
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 Vitordf wrote: 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Replace method - cannot find any data to replace | Excel Programming |