Consulting

Results 1 to 3 of 3

Thread: Solved: Using for next loop

  1. #1
    VBAX Regular
    Joined
    Mar 2005
    Posts
    11
    Location

    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]

  2. #2
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Mar 2005
    Posts
    11
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •