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!
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!