Results 1 to 6 of 6

Thread: VBA Macro - changing a range of cells in a column from No to Yes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,210
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

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

    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

  3. #3
    Board Regular
    Join Date
    Mar 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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 by tanyaleblanc; Apr 1st, 2019 at 01:54 PM.

  4. #4
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,210
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

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

    Hello again,

    Code:
    Sub ReplaceNOs()
    Dim lRow As Long
    lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    Range("M3:M" & lRow).Replace What:="No", Replacement:="Yes", LookAt:=xlPart, SearchOrder:=xlByRows
    End Sub
    Hope this will help

  5. #5
    Board Regular
    Join Date
    Mar 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    worked perfect, thank you again for all your help.

  6. #6
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,210
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

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

    You are welcome ...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •