Cleanup this macro please

R

Revenue

Ok, its been a while since I wrote anything like this customized so I
have forgotten a lot. I recorded the macro below to basically convert
a column of 6 character entries from Text to Values, An example is
201010 where its format is text and it needs to be a numeric 201010. I
can't find a suitable way in excel without using visual basic to
convert the column. Each cell has a different value, just in case
you're wondering that. The recorded macro is :

Sub prmo()

Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VALUE(RC[-1])"
Selection.Copy
Range("G3:G844").Select
ActiveSheet.Paste
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("F6").Select
End Sub
 
J

joeu2004

basically [want to] convert
a column of 6 character entries from Text to Values, An example is
201010 where its format is text and it needs to be a numeric 201010.
I can't find a suitable way in excel

Put the number 1 into a cell and copy it (ctrl-C). Select the cells
with numeric text, right-click and click Paste Special > Multiply >
OK. You can now delete the 1.
 
R

Revenue

Wasn't exactly the approach I had in mind, but I finally incorporated
that approach of multiplying by 1 into a macro and it works. Had to
assign a permanent range name the value of 1, rather than assign 1 to
a variable and then try and copy it and paste special with the
variable.

Thanks for the idea....

Bruce
 
J

joeu2004

Wasn't exactly the approach I had in mind, but I finally incorporated
that approach of multiplying by 1 into a macro and it works.

If you insist on a macro, try:

Sub doit()
Selection.NumberFormat = "General"
x = Selection.Value
Selection.Value = x
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top