VBA Search And Replace
Posted by John on January 21, 2002 10:32 PM
Being new to VBA Im 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.
Heres 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))
Do
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"
