Replace text in column after filtered

[onetap]

Hello,

I'm working on a file and trying to write a code to replace the text in the first column once I apply a filter to the 2nd column. i.e., I filter column 2 for "Dogs" and need to replace column one (which has text of "cat") with "dogs". I have one row of headers which I would like to exclude. So far this is the code I have which isn't working

Selection.AutoFilter
ActiveSheet.Range("$A$1:$CO$4909").AutoFilter Field:=2, Criteria1:="Dog"

Range("A1").Select
ActiveCell.Offset(1, 0).Select
Cells(Columns("A").Rows.Count, "A").End(xlUp).Select
If ActiveCell.Value = "CAT" Then
ActiveCell.Value = "DOG"
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, -1).Select

End if

End Sub

Or even just a way to replace whatever text is in column A with "Dog", it doesn't have to say cat for it to be replace. In other words, I can select all of the active rows in the first column and have it say dog in each row

 

[Fluff]

Try

Selection.AutoFilter
ActiveSheet.Range("$A$1:$CO$4909").AutoFilter Field:=2, Criteria1:="Dog"

Range("A2:A4909").SpecialCells(xlVisible).Replace "*", "Dog", , , , , False, False

 

[onetap]

Try

Selection.AutoFilter
ActiveSheet.Range("$A$1:$CO$4909").AutoFilter Field:=2, Criteria1:="Dog"

Range("A2:A4909").SpecialCells(xlVisible).Replace "*", "Dog", , , , , False, False

Thank you

To add on to the first question, how would I replace the text in column A if the text in column B was dog:
I found a similar post that showed the below code, but it isn't working

If .Range("B" & r).Value = "Cat" Then
.Range("A" & r).Value = "Dog"
End If

 

[Fluff]

how would I replace the text in column A if the text in column B was dog:

That's what the code I supplied should do. If it's not, what is it doing?

 

[onetap]

That's what the code I supplied should do. If it's not, what is it doing?

I was just curious how to do it without the filter. I have to do this with a lot of items so was thinking it might be easier not using a filter and just replacing the text if it matches a diff column

 

[Fluff]

Ok, this will replace the contents in col A, if col B is Dog

Sub ReplaceNoFilter()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace(Replace("if(@1=""Dog"",""Dog"",@)", "@1", .Offset(, 1).Address), "@", .Address))
   End With
End Sub

 

[onetap]

Ok, this will replace the contents in col A, if col B is Dog

Sub ReplaceNoFilter()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace(Replace("if(@1=""Dog"",""Dog"",@)", "@1", .Offset(, 1).Address), "@", .Address))
   End With
End Sub

Really appreciate your help! going to give this a shot now.

Would you mind breaking this down logically for me if you don't mind?

 

[Fluff]

The first Replace replaces @1 with .Offset(, 1).Address & the second replaces @ with .Address
making the formula

 if(.Offset(,1).Address=""Dog"",""Dog"",.Address)

so for A2 that becomes if(B2=""Dog"",""Dog"",A2)
For some background on Evaluate have a look here
https://bettersolutions.com/excel/functions/vba-evaluate.htm