I have an Excel 2000 speadsheet with 9000 rows of data which someone
imported from some other software.

The one column should contain numeric data, but the import left it
with some preceding spaces and a following asterisk. This needs to be
converted to numerical format which can be referenced by formulae.

I really don't want to plow through 9000 lines and am trying to set up
a macro to do this.

The following keystrokes have the right effect.

F2 (function key to go to edit mode)
Backspace (to delete the training asterisk character)
Enter (which writes back to the cell and moves the cursor to the
next cell below)

My problem in creating the macro a

1. How to get the Macro Record to record an F2 depress
2. How to get the macro to loop

I guess I can just stop it with Ctrl-C when it gets past line 9000

Could someone clip some code here please that I can just paste into
the Macro Editor.?

thanks for the help

.les.

It can be done without a macro:
Select the column!
Choose Edit/Replace!
Enter one space in the Search for field and nothing in the Replace by field!
Click Replace All!
Enter ~* in the Search for field and nothing in the Replace by field!
Click Replace All!

Regards,
Stefi

€žLes Juby€� ezt Ã*rta:

I have an Excel 2000 speadsheet with 9000 rows of data which someone
imported from some other software.

The one column should contain numeric data, but the import left it
with some preceding spaces and a following asterisk. This needs to be
converted to numerical format which can be referenced by formulae.

I really don't want to plow through 9000 lines and am trying to set up
a macro to do this.

The following keystrokes have the right effect.

F2 (function key to go to edit mode)
Backspace (to delete the training asterisk character)
Enter (which writes back to the cell and moves the cursor to the
next cell below)

My problem in creating the macro a

1. How to get the Macro Record to record an F2 depress
2. How to get the macro to loop

I guess I can just stop it with Ctrl-C when it gets past line 9000

Could someone clip some code here please that I can just paste into
the Macro Editor.?

thanks for the help

.les.

Hi

Select the range with data. Be sure that it is formatted as General or
Numeric. Replace all spaces (" ") with nothing. Replace all asterics with
nothing.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Les Juby" wrote in message
...
I have an Excel 2000 speadsheet with 9000 rows of data which someone
imported from some other software.

The one column should contain numeric data, but the import left it
with some preceding spaces and a following asterisk. This needs to be
converted to numerical format which can be referenced by formulae.

I really don't want to plow through 9000 lines and am trying to set up
a macro to do this.

The following keystrokes have the right effect.

F2 (function key to go to edit mode)
Backspace (to delete the training asterisk character)
Enter (which writes back to the cell and moves the cursor to the
next cell below)

My problem in creating the macro a

1. How to get the Macro Record to record an F2 depress
2. How to get the macro to loop

I guess I can just stop it with Ctrl-C when it gets past line 9000

Could someone clip some code here please that I can just paste into
the Macro Editor.?

thanks for the help

.les.

The loop

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
... do your stuff
Next i

F2 simulation

Private Declare Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Declare Function GetKeyboardState Lib "user32" ( _
pbKeyState As Byte) As Long

Const VK_F2 = &H71
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2

Sub SimulateF2()
Dim keys(0 To 255) As Byte

Range("A1").Activate
GetKeyboardState keys(0)
'Simulate Key Press
keybd_event VK_F2, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'Simulate Key Release
keybd_event VK_F2, &H45, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0
End Sub




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Les Juby" wrote in message
...
I have an Excel 2000 speadsheet with 9000 rows of data which someone
imported from some other software.

The one column should contain numeric data, but the import left it
with some preceding spaces and a following asterisk. This needs to be
converted to numerical format which can be referenced by formulae.

I really don't want to plow through 9000 lines and am trying to set up
a macro to do this.

The following keystrokes have the right effect.

F2 (function key to go to edit mode)
Backspace (to delete the training asterisk character)
Enter (which writes back to the cell and moves the cursor to the
next cell below)

My problem in creating the macro a

1. How to get the Macro Record to record an F2 depress
2. How to get the macro to loop

I guess I can just stop it with Ctrl-C when it gets past line 9000

Could someone clip some code here please that I can just paste into
the Macro Editor.?

thanks for the help

.les.