ActiveCell.FormulaR1C1 error 1004

Peralva

New Member
Joined
Nov 18, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Excel Formula:
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

Excel Formula:
="=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:

VBA Code:
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]]
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,811
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your formula is not in R1C1 format (it's A1) and you aren't using commas in it, so you actually need:

Code:
ActiveCell.FormulaLocal = Endereco
 
Solution

Peralva

New Member
Joined
Nov 18, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Your formula is not in R1C1 format (it's A1) and you aren't using commas in it, so you actually need:

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,811
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,313
Messages
5,836,594
Members
430,441
Latest member
SurendraTantia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top