VBA Range.Replace does not work second time-CodePudding

I am very new in VBA. I created 2 buttons. Buttons are just for changing the sheets where the data in the cells come from. For example, I pull the numbers from sheet "ecommerce(D2C)" and when I click the button SaaS, it will pull the data from sheet "SaaS". And for Ecommerce button vice versa. I simply used Range.Raplace method for this. But the problem, SaaS button works, taking the numbers from sheet "SaaS", but when I click Ecommerce, it doesn't change the numbers back from "SaaS" sheet to "Ecommerce" sheet. I would be glad if you can help me with this issue. I don't know where I am making the mistake since I am using almost the same code for both of them.

Sub SaaS()

Cells.Select
Selection.Replace What:="ecommerce(D2C)", Replacement:="SaaS", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select

End Sub

Sub Ecommerce()

Cells.Select
Selection.Replace What:="SaaS", Replacement:="ecommerce(D2C)", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A2").Select

End Sub ```

CodePudding user response：

I think I could reproduce the behaviour you was getting. What I had to do is to change from ecommerce(D2C) to Ecommerce (considering that this is the worksheet name). If you try to use the Replace method with something that would result in an incorrect formula, it just does nothing.

Sub SaaS()

    Cells.Select
    Selection.Replace What:="Ecommerce", Replacement:="SaaS", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("A1").Select

End Sub

Sub Ecommerce()

    Cells.Select
    Selection.Replace What:="SaaS", Replacement:="Ecommerce", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    Range("A2").Select

End Sub

CodePudding user response：

This method is failing for "ecommerce(D2C)" because Excel requires you to put a sheet name of this type within single quotes for use in a formula: e.g. "='ecommerce(D2C)'!A1".

Once you click the "Saas" button, this becomes "='SaaS'!A1", but that one does not require the single quotes, so that Excel automatically adjusts it to "=SaaS!A1". So, now, when you try to switch it back again, it will fail, because you are asking Excel to create a reference to "=ecommerce(D2C)!A1", and it won't allow that.

Hence, the fix is simple, add two single quotes:

Sub Ecommerce()

Cells.Select
Selection.Replace What:="SaaS", Replacement:="'ecommerce(D2C)'", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A2").Select

End Sub