Automation of Keystrokes in Populated Cells.

[Aldini]

I am seeking a way, using an Excel macro, to automatically edit a collection
of cells in the following way.
{F2} {Home} " ' " {Enter}
Of course if there is a better way, I would be happy to learn that also.
The issue is that I repeatedly copy data from a website data page, and paste
it into an Excel 2000 spreadsheet. One of the collumns of data needs to be
handled as text, but the characters are: nn-nnn/nn
where "n" is a number. When Excel in any way processes the characters as
numbers, then the cell ceases to be informative of the intended label.
Thanks

 

[Barb Reinhardt]

You could select the cells and run this macro.

Sub Test
dim myRange as range
Dim r as range

set myRange = Selection

for each r in myrange
r.value = "'" & r.text
next r

End sub

 

[Chip Pearson]

You can do it with code like

Sub AAA()
Dim R As Range
If TypeOf Selection Is Excel.Range Then
For Each R In Selection.Cells
If R.HasFormula = False Then
If R.PrefixCharacter = vbNullString Then
R.Value = "'" & R.Value
End If
End If
Next R
End If
End Sub

Select the cell(s) to modify and run the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)