SendKeys to force enter a cell to update format - starts printing instead

[Rathmann]

Hi All,

I have come upon an issue regarding the SendKeys feature in VBA. i have used this before with succes but for some reason i cannot figure out whats the problem.

I want the code below to enter each cell within a range by sending the keys F2 and ENTER. It will not work... It keeps launching printing..

Please Help!

Thank you very much in advance!

Sub F2Macro()
'
' F2Macro Macro
'
' Keyboard Shortcut: Ctrl+t
'
Dim Cell As Range


With ActiveSheet
    For Each Cell In Range("Q1:Q200")
        If IsEmpty(Cell.Value) = False Then
         SendKeys ("{F2}")
         SendKeys ("{ENTER}")
        End If
    Next
End With


End Sub

 

[Norie]

What are you trying to do with the cells?

 

[Rathmann]

Well, this code is part of a much much larger macro. Column q contains a bunch of measurement margins, in this case say 0,76 and 0,86. Part of the larger macro is then to find the lowest of the values in the column Q and provide some more data which is located on the same row but in different columns. To do this i am using the Index, match. All this works fine except my excel (being danish, i think) refuses to search for a value starting with "0," with the format "0,76" insted it always searches with the format "0.76" and therefore it is not finding any matches. So what i did was make the macro convert the range format of column Q tot ext so it would not be considered a value by excel and therefore i could now search for it. This means i at a later point in the macro need to reverse the format back to value so that it now can use the index, match function. When i convert it back, i need to enter the cell before the format really changes and that is the whole idea of the code above.

May seem complicated but long story short, i need it to enter each cell in the range to change the format.

Hope it helps

Thanks

 

[Norie]

You probably don't need to do this conversion, it should be possible to deal with the problem in the code/formulas.

Even if you do need to do the conversion it should be possible to do it without using SendKeys.

Can you clarify exactly what the problem is with the values in column Q?

Is it that they aren't being recognised as numerical values?