Solved: Using for next loop

The code below replaces and moves the strings. Anytime it does not find one of the strings it will skip over the replacement, but it will move the string to the correct destination. I have tried adding in code where if lets say "(HEATHER)" did not exist it would skip that part and go to the next name I wanted to replace. So far I have been unable to get this to work.

Thanks,

gj0519


[VBA]Sub Fix_Names()
With Worksheets(2)
Worksheets(2).Columns(3).Replace _
What:="(HEATHER)", Replacement:="HEATHER", _
Searchorder:=xlByColumns, MatchCase:=True
Range("C1").Select
Selection.Cut Destination:=Range("A6")
End With

With Worksheets(2)
Worksheets(2).Columns(3).Replace _
What:="(JANICE)", Replacement:="JANICE", _
Searchorder:=xlByColumns, MatchCase:=True
Range("C10").Select
Selection.Cut Destination:=Range("A15")
End With

With Worksheets(2)
Worksheets(2).Columns(3).Replace _
What:="(SHERRY)", Replacement:="SHERRY", _
Searchorder:=xlByColumns, MatchCase:=True
Range("C19").Select
Selection.Cut Destination:=Range("A24")
End With

With Worksheets(2)
Worksheets(2).Columns(3).Replace _
What:="(LINDAL)", Replacement:="LINDA", _
Searchorder:=xlByColumns, MatchCase:=True
Range("C28").Select
Selection.Cut Destination:=Range("A33")
End With

With Worksheets(2)
Worksheets(2).Columns(3).Replace _
What:="(CORAL)", Replacement:="CORAL", _
Searchorder:=xlByColumns, MatchCase:=True
Range("C37").Select
Selection.Cut Destination:=Range("A42")
End With
End Sub[/VBA]

Hi gj0519,

Try:
[vba]Sub Fix_Names()
Dim FndRG As Range
With Worksheets(2)
Set FndRG = .Columns(3).Find(What:="(HEATHER)", _
SearchOrder:=xlByColumns, MatchCase:=True)
If Not FndRG Is Nothing Then
.Columns(3).Replace What:="(HEATHER)", Replacement:="HEATHER"
.Range("C1").Cut Destination:=.Range("A6")
End If

Set FndRG = .Columns(3).Find(What:="(JANICE)")
If Not FndRG Is Nothing Then
.Columns(3).Replace What:="(JANICE)", Replacement:="JANICE"
.Range("C10").Cut Destination:=.Range("A15")
End If

Set FndRG = .Columns(3).Find(What:="(SHERRY)")
If Not FndRG Is Nothing Then
.Columns(3).Replace What:="(SHERRY)", Replacement:="SHERRY"
.Range("C19").Cut Destination:=.Range("A24")
End If

Set FndRG = .Columns(3).Find(What:="(LINDAL)")
If Not FndRG Is Nothing Then
.Columns(3).Replace What:="(LINDAL)", Replacement:="LINDA"
.Range("C28").Cut Destination:=.Range("A33")
End If
Set FndRG = .Columns(3).Find(What:="(CORAL)")
If Not FndRG Is Nothing Then
.Columns(3).Replace What:="(CORAL)", Replacement:="CORAL"
.Range("C37").Cut Destination:=.Range("A42")
End If
End With
End Sub[/vba]
It creates a Range variable called FndRG. It first searches for the name, if it is found then it performs the replace, then the cut/paste.
You'd get shorter code by using a separate function to do the search/replace/cut/paste, and you could add more names much easier (and change the locations much easier as well). Take a look at:
[vba]Sub Fix_Names()
FixAName 2, "(HEATHER)", "HEATHER", "C1", "A6"
FixAName 2, "(JANICE)", "JANICE", "C10", "A15"
FixAName 2, "(SHERRY)", "SHERRY", "C19", "A24"
FixAName 2, "(LINDAL)", "LINDA", "C28", "A33"
FixAName 2, "(CORAL)", "CORAL", "C37", "A42"
End Sub
Public Function FixAName(ByVal vWorksheetNum As Long, ByVal vWhat As String, _
ByVal vReplacement As String, ByVal vRange As String, ByVal vDestination As String)
Dim FndRG As Range
With Worksheets(vWorksheetNum)
Set FndRG = .Columns(3).Find(What:=vWhat)
If Not FndRG Is Nothing Then
.Columns(3).Replace What:=vWhat, Replacement:=vReplacement
.Range(vRange).Cut Destination:=.Range(vDestination)
End If
End With
End Function[/vba]

If you have any questions, let me know!
Matt

Thanks Matt,

I believe this is what I am missing.
I will use the shorter code since I do need to add or remove names at times.

Thanks again,
Glenn