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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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,101,851
Messages
5,483,322
Members
407,393
Latest member
GeorgeBrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top