ActiveCell.FormulaR1C1 error 1004

[Peralva]

Hi

I have a Index Match function that uses ranges from another workbook, but its name changes every month. I want to avoid using Indirect, so I'm trying the following steps:

1) I use a formula to write the correct address/workbook name

C62 = C:\Users\Planilhas\Dashboards de Crédito\[Relatório de Crédito - 202109.xlsm]

The "202109" means September 2021 and changes every month.

2) I write a string on another cell with the formula I need

="=INDEX('"&Apoio!$C$62&"Dashboard Carteira'!$G$159:$G$500;MATCH(B3;'"&Apoio!$C$62&"Dashboard Carteira'!$B$159:$B$500;0))"

It returns: C63 =INDEX('C:\Users\Planilhas\Dashboards de Crédito\[Relatório de Crédito - 202109.xlsm]Dashboard Carteira'!$G$159:$G$500;MATCH(B3;'C:\Users\Planilhas\Dashboards de Crédito\[Relatório de Crédito - 202109.xlsm]Dashboard Carteira'!$B$159:$B$500;0))

Now I need to use a macro to paste this formula into another cell:

Endereco = Sheets("Apoio").Range("C63")
    Range("Q9").Select
    ActiveCell.FormulaR1C1 = Endereco

However, I get a Run-time error 1004: Application-defined or object-defined error. How can I fix this?

This problem I'm having seems similar to this [link=[URL]https://www.mrexcel.com/board/threads/insert-variable-into-activecell-formular1c1-in-vba.801206/]one[/link[/URL]]

 

[RoryA]

Your formula is not in R1C1 format (it's A1) and you aren't using commas in it, so you actually need:

ActiveCell.FormulaLocal = Endereco

 

[Peralva]

Your formula is not in R1C1 format (it's A1) and you aren't using commas in it, so you actually need:

ActiveCell.FormulaLocal = Endereco

Thank you!!

FormulaLocal solved my problem. Commas weren't actually wrong because my Excel uses semicollons instead of commas in formulas.

 

[RoryA]

Commas weren't actually wrong because my Excel uses semicollons instead of commas in formulas.

I assumed that, but if you use Formula or FormulaR1C1 you have to use commas.