Macro for F2 "Enter" for all cells in worksheet

[cnscpa]

I have an Excel dashboard workbook. When I go offsite, the formulas error out because I am off the network (so Excel can't link up to the other software). The only way to "re-activate" the formulas is to hit "F2" and "Enter" in each cell. One of the worksheets has 90,000 formulas.

How do you write a MACRO that will look at each cell and do "F2" and "Enter" so I don't have to manually?

Thx

 

[Trebor76]

Hi cnscpa,

Welcome to the forum!!

Can you state what range you're using, or at least the starting row number / column letter to the last column letter? I could post a macro that could check all cells, but it would be far more efficient if it was narrowed down.

Regards,

Robert

 

[Trebor76]

I actually have to sign off now for a few hours, so post back re how this goes:

Sub Macro2()
    
    Dim rngCell As Range
    
    Application.ScreenUpdating = False
        
    For Each rngCell In Cells.SpecialCells(xlCellTypeFormulas)
        '//Calculate every formula cell in the active sheet//
        'Note that it's far better to calculate each cell _
        (effectively the same as pressing "F2") than using _
        SENDKEYS to mimic "F2" and "Enter".
        rngCell.Calculate
    Next rngCell
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[Richard Schollar]

I have an Excel dashboard workbook. When I go offsite, the formulas error out because I am off the network (so Excel can't link up to the other software). The only way to "re-activate" the formulas is to hit "F2" and "Enter" in each cell. One of the worksheets has 90,000 formulas.

How do you write a MACRO that will look at each cell and do "F2" and "Enter" so I don't have to manually?

Thx

Do a FInd/Replace on the cells, finding = and replacing with =. Naturally, you can program a macro to do this for you.