Visual Basics macro to automate Pressing of the F2 Key

Hi, 
  I am looking for the programming behind the key "F2" & "Enter" in Microsoft
Excel. I am not interested in how to use the "sendkeys" function. (it
doesn't work on my pc)
  Currently I need to go to excel, in each of the cells and press "F2"
and "Enter" for excel to recognise my data. I want to know what is the
coding behind the key "F2" as well as "Enter" and how I can use visual basics to
run(automate) this function. 
I hope this is clear. Please e-mail me for any clarification

Hello Shlodge,

The "programming" behind the F2 and Enter keys is pretty straight forward.

F2 maps to
  Selection.Cut

and the Enter key after such an action maps to
  ActiveSheet.Paste

I determined this information by performing the following steps:

[1] Use the menu 
  Tools -> Macro -> Record New Macro
and then selected OK to proceed to recording. At this point on my
machine, a toolbar appears which includes the button needed to stop
recording.

[2] Press F2 and Enter in order. That records those two steps into the macro.

[3] Stop recording by selecting the square button icon on the toolbar
or using the menu
  Tools -> Macro -> Stop Recording

At this point, you can then use the menu
  Tools -> Macro -> Macros...
and select your macro (by default would be named something like
Macro1) and then Edit to bring up Visual Basic with the code displayed
in the window. On my system, the complete macro function was as
follows:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/13/2006 by Maniac
'

'
    Selection.Cut
    ActiveSheet.Paste
End Sub

As you can see above, there are only two actions recorded, that refer
to the two steps (F2 and Enter) respectively.

Now, this macro only does this step for a single cell. To do so for
all the cells of the worksheet, you need something like the following:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/13/2006 by Maniac
'

'
    lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    lastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    For myRow = 1 To lastRow
        For myColumn = 1 To lastColumn
            ActiveSheet.Cells(myRow, myColumn).Cut
            ActiveSheet.Cells(myRow, myColumn).Select
            ActiveSheet.Paste
        Next myColumn
    Next myRow
End Sub

which performs the key steps (cut / paste) in sequence for each
"active" cell of the worksheet.

However, I have a follow up question that if you answer, may lead to a
"better" solution.
  What symptom do you see that requires the cut / paste actions to correct?
If you can answer this question, please make a request for
clarification with the answer and I would be glad to follow up with
another solution to your problems.

I am guessing you are having problems with data imported by Excel (I
had similar problems on my own worksheets) but if that is the cause, I
may be able to help you load the data in a slightly different way to
fix your original problem.

If you have any problems understanding the answer or if you consider
the answer incomplete, please make a clarification request and I would
be glad to expand on the answer as needed.

Good luck with your work.
  --Maniac

---

Request for Answer Clarification by shlodge-ga on 13 Jul 2006 21:06 PDT

Hi Maniac,
Yes, you are correct I am having problem with data import.

The best way I can explain it is like this.
Select a cell (can be any). Use "Format" -  "Cells", and change it to "text"
Type in "31/10/06" or in your case it may be "10/31/06"

After you enter the data, go to "Format" - "Cells", and change it to
"Date" or "General". Maybe use the format as "DD-MMM-YY"

You will notice that the cell would not have changed, the cell will
still display "31/10/06", as a text.

However, if you go into the cell, and press "F2", and then "Enter",
you will have noticed that Excel now recgonises the date and changes
it to "31-Oct-06".

I have tried to do "Edit", "Paste Special, Values" and it didn't work.
It just stays as the text "31/10/06"

When I use the VB code, it is the same as the Paste Special method above
ActiveCell = ActiveCell.Value


When I use the following VB Code

Range("A1") = Range("A1").Value 

it actually interprets the other way round (For example, "7/10/06" is
interpreted as "Oct 7 2006", but when I use "F2" and "Enter" on the
same cell, it will return "7th July 2006").
Please note that the VB code and the "F2" and "Enter" are done at the
same time. I don't know why the VB region setting is different to my
excel region setting. If there is a way to change it, please let me
know.

My problem is that this is how I get the data and I can't change it.

Please note that my region setting has been set to read "dd-mmm-yy". 

Thank you very much for your help.

---

Request for Answer Clarification by shlodge-ga on 14 Jul 2006 01:54 PDT

Not to worry, I finally figured it out after many hours of tearing my
hair out. I just use text to columns

    Columns("A:A").EntireColumn.Select 
    Application.CutCopyMode = False 
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _ 
        FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True

Thanks for your help.

---

Clarification of Answer by maniac-ga on 14 Jul 2006 13:33 PDT

Hello Shlodge,

I am glad you have solved your problem. Let me comment on some of the
symptoms you refer to and perhaps explain the causes of those
symptoms.

[1] Cell value as "text" is not automatically converted to a date

Microsoft Excel stores dates as an integer value. See
  http://www.cpearson.com/excel/datetime.htm
for a nice explanation of the date / time format (as well as a
compatibility "bug" with Lotus 1-2-3).

The value you entered is a string (not an integer). An alternative for
converting the value would be to use code like
  Selection.Value = CDate(Selection.Value)
after you convert the cell type to General or a date type. I did a
quick check without converting the cell type (was text) and the test
macro I used changed the format of the date, but left it as a text
string.

[2] Behavior of Paste Special -> Value menu

Again, the value is a string and not a number, so the conversion won't
happen. You can also show this behavior by entering the following
value into a date formatted cell
  '10/07/06
(note a leading single quote and no trailing quote)
This kind of entry forces a string value into the cell and generally
ignores the formatting settings. If you compare two cells (one with a
date value, the other with a text value), one will be right aligned
and the other will be left aligned - providing another clue of the
type of data stored in the cell.

[3] The "wrong" interpretation of the value in .Value assignment

I reviewed the VB Help files and did a search for
  locale definition
for an explanation that on my system indicates in part that "locale
aware" functions should obey your system locale settings, but VB
itself is fixed as an US-English locale. You may be getting this
latter behavior in the specific assignment of
  Range("A1") = Range("A1").Value
you provided as an example. Odd - a further review of the VB help file
didn't indicate if CDate was locale aware or not.

Again, good luck with your work.
  --Maniac