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]

It's still changing the heading title from Cancel to Yes

 

[Fluff]

It doesn't do that for me.
Can you please post the exact you're using, also do you have any merged cells?

 

[tanyaleblanc]

No merged cells, - PS - I figured out how to do the icon thing for the code!!! lol

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


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("a2: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

 

[Fluff]

This line is redundant so remove it

lRow = ActiveSheet.Range("A30000").End(xlUp).Row

How many rows of data do you have in the sheet?

 

[tanyaleblanc]

I've removed the second line above and there's approx 21000 lines of data

 

[Fluff]

This shouldn't be a problem, but try changing this

lRow = .Range("A30000").End(xlUp).Row

to

      lRow = .Range("A" & Rows.Count).End(xlUp).Row

Also does col A contain data in every row?

 

[tanyaleblanc]

no, A does not contain data in every row.

 

[Fluff]

That's probably what the problem then.
Is there any column where the cells will always contain something?

 

[tanyaleblanc]

yes, column G will always have the acct# in those cells.

I changed this to G and works like a charm, thank you. Also, do you know if when you post a thread if you always get a reply, I posted a new thread yesterday but haven't received a response on it.

lRow = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row

Thank you again,

 

[Fluff]

In that case use

lRow = .Range("[COLOR=#ff0000]G[/COLOR]" & Rows.Count).End(xlUp).Row