Function that processes cells' values returning blank

Question

I would like to be able to loop through a range of cells and pass each cell into a function, that modifies its text and returns it. However, I can't get the below code to work as intended.

Any suggestions?

For Each cell In Worksheets("test").Range("A2:A" & LstRw)
    cell.Value = cleanCell(cell)
Next cell

Public Function cleanCell(cell As Range) As Variant

            If InStr(cell.Value, "US") > 0 Then
                If InStr(cell.Value, "US Test") > 0 Then
                Else
                    cell = Replace(cell.Value, " US", " US Test")
                End If
            End If
                  
End Function

Try using cleanCell = Replace(cell.Value, " US", " US Test") instead of cell = Replace(cell.Value, " US", " US Test"). cell is passed byRef. — FaneDuru

Tim Williams Tim Williams · Accepted Answer · 2020-09-02T17:51:14

For Each cell In Worksheets("test").Range("A2:A" & LstRw)
    cell.Value = cleanCell(cell)
Next cell


Public Function cleanCell(cell As Range) As Variant
    Dim v
    v = cell.Value
    If InStr(v, " US") > 0 Then 'test with space ?
        If Not InStr(v, " US Test") > 0 Then
            v = Replace(v, " US", " US Test")
        End If
    End If
    cleanCell = v 'return original or modified value
End Function

Function that processes cells' values returning blank

1 Answers