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. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 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.

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

    jckplck (2013-02-13)

  4. #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

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 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.

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

    jckplck (2013-02-13)

  7. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 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.

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

    jckplck (2013-02-13)

  9. #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
  •