Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default Cleanup this macro please

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,059
Default Cleanup this macro please

On Nov 11, 12:16*pm, Revenue wrote:
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.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default Cleanup this macro please

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




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,059
Default Cleanup this macro please

On Nov 11, 2:08*pm, Revenue wrote:
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Spreadsheet cleanup help Mike Miller Excel Programming 3 October 30th 09 07:28 PM
Can someone help Cleanup my recorded macro Kelly******** Excel Discussion (Misc queries) 0 April 18th 08 07:51 PM
SORTING MACRO TO CLEANUP MESSY SHEET stefsailor Excel Programming 10 March 16th 08 03:55 PM
Code cleanup peter.thompson[_53_] Excel Programming 2 January 18th 06 07:16 AM
Log File CleanUp [email protected][_2_] Excel Programming 1 December 7th 04 07:19 PM


All times are GMT +1. The time now is 02:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"