VBA selective hardcoding / Paste values if string in cell matches specific string

Question

First time posting for me and hoping to get some help with VBA for selective hardcoding.

I currently have a column into which a formula is set which returns either blank or a variety of text strings (the status of our company's orders).

I need to make a macro that looks into all the cells of that column and copy/pastes as value into that same cell only if the formula in that cell returns text string "Received". It should not affect the other cells where the formula is returning either blank or a different text string.

Would really appreciate your help. Please let me know if you need more info.

Thanks in advance,

Olivier

Answer 1

Put the following in the VBA project of your workbook:

Option Compare Text

Sub replaceThem()
Dim r As Range
Dim c

Set r = Range("B1:B3") ' use the actual range here
For Each c In r
  If c.Value = "Received" Then c.Formula = "Received"
Next

End Sub

This will do what you asked. c.Value returns the value of the formula in the cell c, c.Formula replaces the formula. The Option Compare Text makes the comparison case-insensitive.