VBA Macro - changing a range of cells in a column from No to Yes

[tanyaleblanc]

I'm trying to change the word No to Yes in column M I have headings in row 2, so anything after that to the last cell in column M I want to change to Yes, but I'm getting an error.

Sub Filter()
Dim lRow As Long
Dim fnd As Variant
Dim rplc As Variant
fnd = "No"
rplc = "Yes"
If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
lRow = ActiveSheet.Range("A8500").End(xlUp).Row

With ActiveSheet
.AutoFilterMode = False
With Range("a1:ah" & lRow)
.AutoFilter
.AutoFilter field:=2, Criteria1:="#N/A"
.AutoFilter field:=14, Criteria1:="="
.AutoFilter field:=16, Criteria1:="="
.AutoFilter field:=17, Criteria1:="="
.AutoFilter field:=18, Criteria1:="="
.AutoFilter field:=19, Criteria1:="="
.AutoFilter field:=13, Criteria1:="<>Yes"
'trying to find and replace no with yes in column m
Range("m3") = "Yes": Range("m3:m" & lastrow).FillDown


End With
End With



End Sub

 

[James006]

Hello,

First remark : why are you the variable lastrow ... instead of lRow .... in your last line ...

Second remark : why not simplifying your life with

YourRange.Replace What:="Yes", Replacement:="No", LookAt:=xlPart, SearchOrder:=xlByRows

Hope this will help

 

[tanyaleblanc]

First - I just realized after I sent this I had lastrow instead of lrow
Second - this does not work, it's not replacing my no's with Yes's. It's not replacing anything actually.

Range("m3:M" & lRow).Replace What:="Yes", Replacement:="No", LookAt:=xlPart, SearchOrder:=xlByRows

I'm very new to this so I apologize if I don't know the lingo.

 

[James006]

Hello again,

Sub ReplaceNOs()
Dim lRow As Long
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
[COLOR=#333333]Range("M3:M" & lRow).Replace What:="No", Replacement:="Yes", LookAt:=xlPart, SearchOrder:=xlByRows[/COLOR]
End Sub

Hope this will help

 

[tanyaleblanc]

worked perfect, thank you again for all your help.

 

[James006]

You are welcome ...