Looping through the F2 command

  • Thread starter Thread starter CDB
  • Start date Start date
C

CDB

I am currently using Microsoft 2000 and as part of my job
I need to download figures into a spreadsheet and do
analysis on these figures. The problem is that when I
download the figures they must get imported as text, ans
they are not recognised until I go through each cell
pressing the F2 key. I can have thousands of rows at a
time, so this can take quite long.

Is there any code I can use to just set up a macro to loop
through the cells with data in and press the F2 key?
 
Try code like the following:

Dim Rng As Range
For Each Rng In Selection.Cells
Rng.Value = Rng.Value
Next Rng

Select the cells containing the data, then run the code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi

Sub F2_update()
Dim c As Range

For Each c In Selection
SendKeys "{F2}", True
SendKeys "{ENTER}", True
Next c
End Sub

Just highlight the rows/columns you want to process and start this
macro
Frank
 
if the values are numbers, you can format the column as Number under
format=>Cells, number tab.

then select a blank cell and do edit copy. Select the cells in the column
and do Edit=>PasteSpecial and select Values and Add. This should have the
same effect as selecting each cell and editing it. An alternative is to
select the column and do Data=>Text to columns and in the wizard, select
delimited, then select a delimiter that does not appear in the entries.
This should also cause Excel to reevaluate the values in the cells and store
them as numbers.

A macro (select the cells you want converted)

Sub Convert()
Dim cell as Range
selection.Numberformat = "#,##0.00"
for each cell in selection.SpecialCells(xlConstants, xlTextValues)
cell.Formula = cell.Value
Next
selection.Numberformat = "General"
End Sub
 
Back
Top