Formula with dynamic archive name as a variant

[Dj Jack]

Hi Again... good nights..

I try to adjust this formula to somthing that works... i have something like this..

Range("D18").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('[Calculadora Tecnica JULIO.xlsm]IN Y CS'!R18C4:R18C37)"
Range("D18").Copy
Range("D18:AL18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

The Bold section have a formula that refers another book... how i can do dynamic this name i try something like this but doesen't work...

Dim Mesanterior As String
Dim Miarchivoanterior As String

Mesanterior = UCase(MonthName(Month(Date) - 1))
Miarchivoanterior = "F:\Calculadora Tecnica\Calculadora Tecnica" & " " & Mesanterior & ".xlsm"


Range("D18").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('[how insert the variant miarchivoanterior in this place¡?]IN Y CS'!R9C4:R9C37)"


Greetings!!

 

[James006]

Hi,

You should try to use the full path ...starting the root directory such C:\ ...

Hope this will help

 

[Dj Jack]

Hi,

You should try to use the full path ...starting the root directory such C:\ ...

Hope this will help

Hi James, thanks for the fast answer but not work...

I need to tell vba that the formula contains a dynamic file name that changes month by month

Dim Mesanterior As String
Dim Miarchivoanterior As String

Mesanterior = UCase(MonthName(Month(Date) - 1))
Miarchivoanterior = "F:\Calculadora Tecnica\Calculadora Tecnica" & " " & Mesanterior & ".xlsm"


Range("D18").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('[Mesanterior]IN Y CS'!R9C4:R9C37)"

 

[James006]

Hi,

Let me share with a few tests to peform ....

1. Before using a macro .. make sure your formula works on its own ...

2. Once the calculation is correct, you can then decide to move on to the macro and decide if the macro should get the cell to display the final result OR the formula which calcultates the final result ...

Hope this will help