MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Search And Replace

Posted by John on January 21, 2002 10:32 PM

Being new to VBA I’m trying to perform a search on a number of cells contained in the “C” column.
I have a VBA script, which will search for blank cells in column “C” and copy data from adjacent cells in the current row.
But is there a way of searching for a single character in my case an “*” within the range of cell data in column “C” to perform the same copy function.

Here’s my current VBA script.

LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("C" & i).Value = "" Then Range("D" & i & ":E" & i).Copy Destination:=Range("C" & i)
Next i

Posted by colo on January 22, 2002 12:13 AM

Hi, John.
Would you please try this code.
If you want to find wild card character like "*", you can use "find method".

Sub test()
Dim rngDt As Range, rngFound
Set rngDt = Range("C1", Range("A65536").End(xlUp).Offset(, 2))
Set rngFound = rngDt.Find(What:="~*", LookAt:=xlPart)
With rngFound
If Not rngFound Is Nothing Then _
.Value = .Offset(, 1).Value & .Offset(, 2).Value
End With
Set rngFound = rngDt.FindNext(rngFound)
Loop While Not rngFound Is Nothing
End Sub

Posted by John on January 22, 2002 2:19 PM

Thanks for the code, it finds the character "*" and removes the character from the cell in column "C". But is there a way to replace the complete cell value with a value from an adjacent cell once the "*" is found?
As in my original code if a cell in column "C" was empty the code would copy the adjacent cell range "D" to "E" into column "C" and "D"