Conditional find and replace/overwrite VBA

Topic

New Reply

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

33132-ConditionalReplacement

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

That’s not a slight addition – it requires a completely different approach!
Will post back when I have time to have another look.

Reply | Quote