Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Conditional find and replace/overwrite VBA

    Hi Guys,

    I am trying to write a macro that can overwrite certain cells with a specific value. The overwrite should be triggered by a particular value in corresponding cell from another column. Please see the attached spreadsheet.The condition is as follows: for all cells in column D containing "DNA", all corresponding cells in column A should be overwritten by "OPDNA". Can you help please?

    Thanks

    ConditionalReplacement.xlsx

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,873
    Thanks
    0
    Thanked 79 Times in 75 Posts
    This should do it
    Code:
    Sub OverwriteDNA()
        Dim lRow As Long
        lRow = Cells(Rows.Count, "A").End(xlUp).Row
        Range("A2:A" & lRow).Value = Evaluate("IF(D2:D" & lRow & "=""DNA"",""OPDNA"",A2:A" & lRow & ")")
    End Sub
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. The Following User Says Thank You to rory For This Useful Post:

    jckplck (2013-02-13)

  5. #3
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Roy,

    This is absolutely fantastic! Thank you very much!

    Can I be a bit cheeky and ask for a slight addition to this code? Can it also delete rows where column D reads either "EAA" or "TOC" please?

    Thanks

  6. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,873
    Thanks
    0
    Thanked 79 Times in 75 Posts
    That's not a slight addition - it requires a completely different approach!
    Will post back when I have time to have another look.
    Regards,
    Rory
    Microsoft MVP - Excel.

  7. The Following User Says Thank You to rory For This Useful Post:

    jckplck (2013-02-13)

  8. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,873
    Thanks
    0
    Thanked 79 Times in 75 Posts
    Here you go:
    Code:
    Sub OverwriteDNA()
        Dim lLastRow As Long
        Dim lRow As Long
        Application.ScreenUpdating = False
        lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
        For lRow = lLastRow To 2 Step -1
            Select Case LCase(Cells(lRow, "D").Value)
                Case "eaa", "toc"
                    Rows(lRow).Delete
                Case "dna"
                    Cells(lRow, "A").Value = "OPDNA"
            End Select
        Next lRow
        Application.ScreenUpdating = True
    End Sub
    Note: there are more efficient methods if you have a lot of data.
    Regards,
    Rory
    Microsoft MVP - Excel.

  9. The Following User Says Thank You to rory For This Useful Post:

    jckplck (2013-02-13)

  10. #6
    Lounger
    Join Date
    Jan 2013
    Location
    Kent UK
    Posts
    29
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thank you for this, much appreciated. It will save me a lot of fiddling.
    I am quite surprised that the codes look soooo different from each other, especially the overwrite part. Anyway, great stuff. Thanks

Posting Permissions

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