Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    How do i modify this code (Excel xp)

    I am using this macro and presently if the strReplaceStatement matches exactly it works fine. I want, however, to make it work if just a part of the strReplaceStmt is true. For instance, if my strReplaceStement = "now is the time for all good men" I want it to work for even the cells that have "now is the time for all good men to come to the aid". Right now it doesn't do this. How would i modify the statement? thanks

    For I = lLastRow To lStop Step -1
    If Trim(UCase(ActiveSheet.Range("A1").Offset(I, 0))) = strReplaceStatement Then
    ActiveSheet.Range(strRangeSelect).Copy
    ActiveSheet.Range("A1").Offset(I, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Range("A1").Offset(I, 0).PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next I

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do i modify this code (Excel xp)

    jha900,

    Try something along these lines:
    <pre>Sub Test()
    Dim sMsg As String
    Dim sReplaceStatement As String

    On Error GoTo errTest
    ' Err.Raise 11

    sReplaceStatement = "now is the time for all good men"

    Range("A1").Select
    Do While ActiveCell <> ""
    If ActiveCell Like sReplaceStatement & "*" Then
    ActiveCell.Copy
    ActiveCell.Offset(0, 1).PasteSpecial xlPasteValues
    ActiveCell.Offset(0, -1).Select
    End If
    ActiveCell.Offset(1, 0).Select
    Loop

    Application.CutCopyMode = False
    Exit Sub
    errTest:
    sMsg = "An error has been encountered." & vbCrLf
    sMsg = sMsg & "Type: " & Err.Description
    MsgBox sMsg, vbInformation + vbOKOnly, "Error"
    End Sub
    </pre>


    In my example I did not include the pasting of formatting nor did I delete any rows. The key to this is to use the LIKE keyword along with a wildcard character.

    Brent

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do i modify this code (Excel xp)

    See if this will do what you want:

    <pre> For I = lLastRow To lStop Step -1
    If InStr(Trim(UCase(ActiveSheet.Range("A1").Offset(I, 0))), strReplaceStatement) > 0 Then
    ActiveSheet.Range(strRangeSelect).Copy
    ActiveSheet.Range("A1").Offset(I, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Range("A1").Offset(I, 0).PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Range("A1").Offset(I, 0).EntireRow.Delete
    End If
    Next
    </pre>

    Legare Coleman

Posting Permissions

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