[VBA] If Value is found in range, replace with specific text

[MarkRCC]

Hi All,

I want to have a button that will run a macro to change any cell in a range that has the value of "-" to "md" (for 'Missing Data', as this is what the program will detect).

How can I write the code for this? For reference, the column that I would like the changes to be made in is column I and the range is around 800 rows.

Thanks in advance!

 

[MarkRCC]

Sorry, in addition to this, I would also like the cell in Column K to change from 'A' to 'F' when the value is changed from '-' to 'md'

Under normal circumstances I would use an IF statement to change this value automatically based on the value in Column I, but as I am using Paste as Values, I am not able to use formula in these cells on the sheet.

 

[wideboydixon]

Try this macro:

Public Sub ReplaceDashWithMD()

With Range("I1", Cells(Rows.Count, "I").End(xlUp))
    .Replace "-", "md", LookAt:=xlWhole
End With

End Sub

WBD

 

[wideboydixon]

Ah. Didn't see your extra requirement. Try this instead then:

Public Sub ReplaceDashWithMD()

Dim allValues As Variant
Dim thisRow As Long

With Range("I1", Cells(Rows.Count, "K").End(xlUp))
    allValues = .Value
    For thisRow = 1 To UBound(allValues)
        If allValues(thisRow, 1) = "-" Then
            allValues(thisRow, 1) = "md"
            allValues(thisRow, 3) = "F"
        End If
    Next thisRow
    .Value = allValues
End With

End Sub

WBD

 

[MarkRCC]

The second code worked perfectly, thanks a lot, I really appreciate it!