VBA: Issue

[SteveOranjin]

I have some code that makes changes to an entire sheet that I'm working with. I thought I would change it so it would make changes to ONLY the first row. This being because the type of data the code is looking for will only be in the first row.

However, now when I select the formula to operate on a different tab. It doesn't work as it should.

Application.ScreenUpdating = False


Sheets("Data Sheet").Rows("1").Select
     Selection.Replace What:="Product Name", Replacement:="Productname", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Application.ScreenUpdating = True
End Sub

The only difference between my VBA and the VBA in the actual macro is that the macro runs through about forty or fifty of these, instead of just one. Can someone please help me understand why this only works when I am ON the tab in question?

Hope you are well,

Steve

 

[Joe4]

If you are not already on "Data Sheet", you cannot select both the sheet and range at the same time.
One way to do it is to like this:

Sheets("Data Sheet").Activate
     Rows("1").Replace What:="Product Name", Replacement:="Productname", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

or, you do not need to even activate the sheet to perform that replace, just use:

    Sheets("Data Sheet").Rows("1").Replace What:="Product Name", Replacement:="Productname", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[SteveOranjin]

If you are not already on "Data Sheet", you cannot select both the sheet and range at the same time.
One way to do it is to like this:

Sheets("Data Sheet").Activate
     Rows("1").Replace What:="Product Name", Replacement:="Productname", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

or, you do not need to even activate the sheet to perform that replace, just use:

    Sheets("Data Sheet").Rows("1").Replace What:="Product Name", Replacement:="Productname", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

This isn't working, and I'm not clear on why. It appears to me that this should work.

 

[Joe4]

It works for me when I tested it out.
Are you sure that these cells you are trying to replace have EXACTLY "Product Name" in them, with no other text or extra spaces?

 

[Logit]

.
Let me ask ... when you change sheets are you changing the sheet name here :

[COLOR=#333333]Sheets("[/COLOR][COLOR=#ff0000][B]Data Sheet[/B][/COLOR][COLOR=#333333]").Rows("1").Select[/COLOR]

 

[SteveOranjin]

It works for me when I tested it out.
Are you sure that these cells you are trying to replace have EXACTLY "Product Name" in them, with no other text or extra spaces?

When I open and run the macro when I have the sheet open, "Data Sheet", it does what it is supposed to do. When I am on a different sheet it doesn't work.

 

[Joe4]

Though it shouldn't make a difference, try breaking it up into two commands like this and see if that makes a difference:

    Sheets("Data Sheet").Activate
    ActiveSheet.Rows("1").Replace What:="Product Name", Replacement:="Productname", Lookat:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Rick Rothstein]

The only difference between my VBA and the VBA in the actual macro is that the macro runs through about forty or fifty of these, instead of just one.

Show us the loop that you are using to do the above.