Table Formulas not auto calculating!!

[Villups]

Good morning,

This is my first post although I have been looking at this forum and learning from it for a long time.

I found myself in a weird situation when creating a Macro:

1- One of the things I do in my macro is to paste a formula (as a formula) in a cell of an Excel table:
Range(columna & "2").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

2- Once I paste it, this formula is automatically filled in all the cells of my table but it is not calculated!! The only thing that seems to work is manually enter to the first cell and press "Enter"

I have tried setting again calculation to automatic (which is already set), and I have also tried the ".Calculate" command, but none of it works.

Application.Calculation = xlCalculationAutomatic --> not working
Range(columna & "2").Calculate --> not working
Range(columna & "2:" & columna & lrow).Calculate --> not workingWorksheets("XXX").UsedRange.Columns(columna).Calculate --> not working​

I please beg you to help me on this!

Thanks.

Kindest regards,

Villups.

 

[BarryL]

can you please post you're whole code as well as an example of what you are copying/pasting.

 

[Villups]

Yes,

Here is the code:

    ActiveWorkbook.Sheets("Bloq. Espejo").Select
    Range("B1").Select
    ActiveSheet.ShowAllData  '--> Turn off all filters
    lcol = Cells(1, 1).End(xlToRight).Column + 1
    lrow = Cells(1, 1).End(xlDown).Row
    columna = columna_a_letras(lcol)
    Range(columna & "1") = data2 '--> Creates new column after the last one with the actual date
    ActiveWorkbook.Sheets("Fórmulas").Select 
    Range("B2").Copy '--> Copies the formula from the sheet with the formulas
    ActiveWorkbook.Sheets("Bloq. Espejo").Select
    Range(columna & "2").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False '--> Pastes the formula
    Application.CutCopyMode = False
    Range(columna & "2:" & columna & lrow).Copy
    Range(columna & "2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = FalseApplication.Calculation = xlCalculationAutomatic --> not working
Range(columna & "2").Calculate --> not working
    Cells.Replace "#N/A", 0, xlWhole '--> Converts all N/A to 0, so since everything is N/A everything is 0
    Cells.Replace "#VALOR", 0, xlWhole

The images are these ones:

(formula sheet)


(Table sheet "Bloq.Espejo")

The formulas only update if I manually press on one of them with the mouse...

Thanks,

Regards.

Marc.

 

[BarryL]

In your images, you have TD2 on the formulas sheet but TD1 on the other sheet. Is that supposed to be correct.

 

[BarryL]

Try change the formulas on the formulas sheet to

=BUSCARV(Bloq.Espejo!$F:$F,TD1!$A:$B,2,FALSO)

Assuming TD1 sheet name doesn't have spaces.

 

[Villups]

In your images, you have TD2 on the formulas sheet but TD1 on the other sheet. Is that supposed to be correct.

Yes Barry, on B2 the formula is "=BUSCARV($F:$F;'TD1'!$A:$B;2;FALSO)", I took the image pointing to B4, so on the macro this is correct.

I don't understand why do I have to click on the cell in order to update it...

Thanks

 

[BarryL]

can you try the below?

=BUSCARV(Bloq.Espejo!$F:$F,TD2!$A:$B,2,FALSO)

 

[Villups]

can you try the below?

=BUSCARV(Bloq.Espejo!$F:$F,TD2!$A:$B,2,FALSO)

It works Barry! I did not know you had to put the exact sheet even though the formula was in that sheet already,

Thank you very much

Regards,

V.

 

[BarryL]

It works Barry! I did not know you had to put the exact sheet even though the formula was in that sheet already,

Thank you very much

Regards,

V.

Good stuff. Glad you got it sorted.