PDA

View Full Version : advanced find and replace loop



Eddie
07-11-2008, 11:44 AM
I have an excel sheet with a code name in column B and a generic name in column C. I need to search for "vdk" within column B, and if present, change column C to "vodka". Here's what I have so far:

Sub FindAndReplace()


Dim VdkCondition As String
Dim CommonCodeCell As String
Dim ClassCellRow As String
Dim ClassCell As String

Columns("B:B").Select
VdkCondition = Selection.FindNext(What:="vdk", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


CommonCodeCell = ActiveCell.Address
ClassCellRow = Mid(CommonCodeCell, 4, 10)
ClassCell = "$C$" & ClassCellRow

If VdkCondition = True Then

Range(ClassCell).Select
ActiveCell.FormulaR1C1 = "Vodka"

End If


End Sub


I am having trouble with the looping. This code will find the first instance, but not continue after that. There are almost 30,000 entries. I've tried to use findnext, but it's not working too well. Any help would be most appreciated. The file is huge, but I can load it up if that helps at all. Thanks!

Norie
07-11-2008, 11:55 AM
Eh, there is no looping in that code, unless I'm missing something.:eek:

Why don't you actually use the Replace method?

Bob Phillips
07-11-2008, 12:04 PM
You have to do a Find, and then if you find something,m runa a lopp[ with FindNext.

There is a very clear example in VBA help.

Eddie
07-11-2008, 01:18 PM
found it, thanks a lot!

mdmackillop
07-12-2008, 06:24 AM
Eddie,
If your question is solved, please mark it so using the thread tools drop down.
Regards
MD