Keyboard Macro

[MikeDBMan]

I wish to have a macro that just does this:

Hits F2 (for cell edit)
Then Hits enter key

I can't record a macro that does that. It edits the formula. Suggestions?

 

[Joe4]

It is really hard to say why it might be happening without having an intimate knowledge if how/when your VBA code is running, and at when this errors occur and when they don't. It is probably all related.

You may want to have a look here:
http://office.microsoft.com/en-us/excel-help/correct-a-value-error-HP005203950.aspx

and here, especially the part where they mention having non-numeric entries in a cell at the time that it is producing this error:
http://www.ozgrid.com/Excel/formula-errors.htm

 

[MikeDBMan]

I really am not concerned about how it is happening. I am confident it is just a cosmetic thing because of the VBA code that is "getting in the way". I am also confident that were I logged in to that code there would be no problem. But my original question still stands. Is there not a way to have a macro Hit F2 and then the enter key?

 

[VoG]

Maybe this (not tested with a formula that returns an error).

Sub btest()
SendKeys "{F2}"
DoEvents
SendKeys "{ENTER}"
DoEvents
End Sub

 

[Joe4]

I go back to a simple variation of the first code I posted:

Sub RefreshFormula()
    ActiveCell.Formula = ActiveCell.Formula
End Sub

I cannot re-create your exact situation, but it need seem to have the same effect as hitting F2 and hitting Enter (it basically just re-enters the formula you already have in the ActiveCell).

 

[MikeDBMan]

Both subs

BTest()
and
RefreshFormula()
solved my problem.


Thanks!

 

[Joe4]

Excellent!

If there is a how whole range of cells you would like to apply this to simultaneously, we can extend the macros to work over and extended range.

For example, if you highlighted a bunch of cells and ran this macro, it would update all the cells in the selection at the same time:

Sub RefreshFormula()
    Dim cell As Range
    For Each cell In Selection
        cell.Formula = cell.Formula
    Next cell
End Sub