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

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

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

Second remark : why not simplifying your life with
Code:
YourRange.Replace What:="Yes", Replacement:="No", LookAt:=xlPart, SearchOrder:=xlByRows
Hope this will help
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
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.
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello again,

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,021
Messages
5,447,912
Members
405,470
Latest member
manaya

This Week's Hot Topics

Top