How to convert na, NA, Na, or nA to N/A in columns E and F

[Eloise T]

The following macro successfully changes any characters in Columns E and F to upper case.

I added a 2nd line to convert/change/replace any occurrence of na, Na, nA, or NA in Columns E and F to N/A:

.Value = .Parent.Evaluate(Replace("IF(#=""na"",UPPER(#),""N/A"")", "#", .Address))
Currently the code wholesale changes everything found in Columns E and F to N/A....and that's not good.

Thank you in advance.

Sub ChangeCase()

'               Affects <Columns E and F>

    With Ws.[E3:F3].Resize(Ws.Cells(Rows.Count, "D").End(xlUp).Row)  '.Row - 2)

'        This changes all characters in the array defined by Columns E and F to UPPER case.
        .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))

'        I need the following line to change    na, Na, nA, or NA to N/A
'        In theory, any occurrence of na, Na, and nA are already changed to NA by virtue of the previous line containing the UPPER function, so the following line only needs to add a forward slash (/) to the NA.
        .Value = .Parent.Evaluate(Replace("IF(#=""na"",UPPER(#),""N/A"")", "#", .Address))

    End With
End Sub

 

[GCExcel]

Hi,

I think this should work:

.Value = .Parent.Evaluate(Replace("IF(#=""NA"",""N/A"", UPPER(#))", "#", .Address))

 

[shrivallabha]

Maybe use:

.Replace "NA", "N/A", xlWhole

instead of evaluate route which you are using.

 

[Eloise T]

Both shrivallabha's and GCExcel's solutions worked and within a second of each when timed. Thank you both.