0

Suppose that my Excel workbook contains three worksheets called Object1, Object2 and Calculations.

  • In worksheet Object1, cell A1 is called Object1_Beginning and contains the value 250.
  • In worksheet Object2, cell B1 is called Object2_Beginning and contains the value 750.

In worksheet Calculations, cell A1 contains a formula of the following form:

=IF(INDIRECT(CONCAT("'Object1'!", ADDRESS(ROW(Object1!Object1_Beginning), COLUMN(Object1_Beginning)))) < 500, 1, 0)

(I do not know why Excel sometimes removes the ' characters when writing the names inside the =ROW() and =COLUMN() functions but it works just fine. I also know that using both =INDIRECT() as well as =CONCAT() is not actually needed in this case but I have a more complex formula at work where it is required in a similar way as shown in this example.)

Then, I decided to write a macro that could replace the string Object1 in 'Calculations'!A1’s formula with Object2 so that the result should look like this:

=IF(INDIRECT(CONCAT("'Object2'!", ADDRESS(ROW(Object2!Object2_Beginning), COLUMN(Object2_Beginning)))) < 500, 1, 0)

The macro code used for this purpose is written as follows:

ThisWorkbook.Sheets("Calculations").Range("A1").Formula = Replace(ThisWorkbook.Sheets("Calculations").Range("A1").Formula, "Object1", "Object2", , , vbTextCompare)

However, the result then looks like this:

=IF(@INDIRECT(CONCAT("'Object2'!", ADDRESS(ROW(Object2!Object2_Beginning), COLUMN(Object2_Beginning)))) < 500, 1, 0)

I cannot remove the additional @ character before the =INDIRECT() function by using the Replace() function in VBA. In this simple case, the formula still works and can calculate the correct result. Under more complex circumstances, however, this is not the case. Only when I then manually remove all the @ characters, the calculation of the modified formula works correctly.

What can I do? I suppose that it has something to do with the =INDIRECT() function being nested inside the =IF() function but I cannot figure out how do fix this issue.

Nemgathos
  • 605
  • 1
  • 5
  • 13
  • 1
    An annoying problem indeed. Probably a duplicate of [this](https://stackoverflow.com/a/61138478/9758194) in a way. Please try `.Formula2` instead. – JvdV Apr 13 '21 at 11:52
  • 1
    This has worked perfectly! Thank you very much. I did not find this question and it also has not been suggested to me. My new macro code now looks like this: `ThisWorkbook.Sheets("Calculations").Range("A1").Formula2 = Replace(ThisWorkbook.Sheets("Calculations").Range("A1").Formula2, "Object1", "Object2", , , vbTextCompare)` – Nemgathos Apr 13 '21 at 12:03
  • 1
    Glad it helped. Let me sign this as a duplicate =) – JvdV Apr 13 '21 at 12:04
  • Ok. Should I delete my question now or should I let it exist so that people may find the solution to this problem when they are specifically looking for “the `@` character problem”? – Nemgathos Apr 13 '21 at 12:49
  • 1
    Definately let it exist. It appears to be hard to find "@" in search queries but any lead can help =) – JvdV Apr 13 '21 at 12:52

0 Answers0