Sub GEN_FIX_SCN()
'Created 01/25/2008 by Ron Willoughby
Dim AWK_NME, CHK_NME, END_ROW, MSG1, TITLE1
Dim x As Double
AWK_NME = ActiveWorkbook.Name
MSG1 = "Currently, the Active Workbook is named: " + AWK_NME _
+ ". Is this the Workbook with which you want to begin" _
+ "this program? (Y/N)"
TITLE1 = "CHECK WORKBOOK NAME"
CHK_NME = UCase(InputBox(MSG1, TITLE1, "N", 5000, 5000))
Select Case CHK_NME
Case Is <> "Y"
Exit Sub
End Select
Application.Goto Reference:="R2C1" 'First cell of the column to be fixed
Selection.End(xlDown).Select 'Finds last row of selection
'EMPTY CELLS IN THE COLUMN WILL NOT WORK! FILL WITH A SINGLE SPACE.
END_ROW = Selection.Row 'Defines END_ROW
Application.Goto Reference:="R2C1:R" & Trim(END_ROW) & "C1"
'Selects current region
Selection.NumberFormat = "@" 'Formats region as text
Application.Goto Reference:="R2C1" 'First cell of the column to be fixed
For x = 1 To END_ROW
If Selection = "" Then
MsgBox "Done!"
Range("A2").Select
Exit Sub 'Ends routine at empty cell
End If
Selection = Mid(Selection, 1, Len(Selection))
Application.Goto Reference:="R[1]C1" 'Advances to next row in column
Next x
MsgBox "Done!"
Range("A2").Select
End Sub
'This fixes any text displayed as a scientific number; if the data contains a
'leading apostrophe or space (or any other extra character), change line 18 to
'read:
'Selection = Mid(Selection, 2, Len(Selection)-1)