VBA Macro - If word cancelled in one column, enter Yes in another column

[tanyaleblanc]

trying to say; if the word cancelled in column b, enter yes in column ab


Sub Filter()
Dim lRow As Long
Dim ts As Date
If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData
lRow = ActiveSheet.Range("A30000").End(xlUp).Row



With Workbooks("Rental Rec.xlsm")
With .Sheets("Owned")
If .AutoFilterMode And .FilterMode Then .ShowAllData
lRow = .Range("A30000").End(xlUp).Row
With .Range("a1:bh" & lRow)
.AutoFilter Field:=2, Criteria1:="<>#N/A"
.AutoFilter Field:=3, Criteria1:="Payout"
End With
.AutoFilter.Range.Offset(1).Columns(2).ClearContents
End With
End With



.Range("ab3:ab" & lRow).Value = "Yes"

 

[tanyaleblanc]

I received an email stating Joe4 replied, but I don't see this reply here.

 

[Fluff]

Try

Sub Filter1()
Dim lRow As Long
Dim ts As Date

With Workbooks("Rental Rec.xlsm")
   With .Sheets("Owned")
      If .AutoFilterMode And .FilterMode Then .ShowAllData
      lRow = .Range("A30000").End(xlUp).Row
      With .Range("a1:bh" & lRow)
         .AutoFilter Field:=2, Criteria1:="<>#N/A"
         .AutoFilter Field:=3, Criteria1:="Payout"
      End With
      .AutoFilter.Range.Offset(1).Columns(2).ClearContents
      .AutoFilter.Range.Offset(1).Columns(28).Value = "Yes"
   End With
End With
End Sub

Also there is no point in including this line all the time as it does nothing whatsoever

If AutoFilterMode = True And FilterMode = True Then ActiveSheet.ShowAllData

Also:
When posting code, please use code tags, the # icon in the reply window.

 

[Joe4]

I received an email stating Joe4 replied, but I don't see this reply here.

You have posted three questions today that had a lot of similarities, and at first I thought it was a a duplicate post (which would have been against forum rules: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html). I looked a little further in depth, and noticed slight differences, so I removed it.

 

[tanyaleblanc]

This worked but it applied the word Yes to two rows and it should have only populated once. The criteria only had 1 row and populated yes in two rows. It should have only applied it once.

[TABLE="width: 375"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Will Not Bill This Month[/TD]
[TD]Comm WH - Excluded[/TD]
[TD]Excluded [/TD]
[TD]Payout[/TD]
[TD]Cancel[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]No[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Sub Filter1()
Dim lRow As Long
Dim ts As Date

With Workbooks("Rental Rec.xlsm")
   With .Sheets("Owned")
      If .AutoFilterMode And .FilterMode Then .ShowAllData
      lRow = .Range("A30000").End(xlUp).Row
      With .Range("a1:bh" & lRow)
         .AutoFilter Field:=2, Criteria1:="<>#N/A"
         .AutoFilter Field:=3, Criteria1:="Payout"
      End With
      With .AutoFilter.Range.Offset(1)
         .Columns(2).ClearContents
         .Resize(.Rows.Count - 1).Columns(28).Value = "Yes"
      End With
   End With
End With
End Sub

 

[tanyaleblanc]

It still populated in 2 rows but this time it changed the Heading from Cancel to Yes and yes in the row it should be.

[TABLE="width: 75"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 225"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Excluded [/TD]
[TD]Payout[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Do you have a header in row1?

 

[tanyaleblanc]

I have a header in row 2

 

[Fluff]

In that case change this line as shown

With .Range("a[COLOR=#ff0000]2[/COLOR]:bh" & lRow)