Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet cleanup help | Excel Programming | |||
Can someone help Cleanup my recorded macro | Excel Discussion (Misc queries) | |||
SORTING MACRO TO CLEANUP MESSY SHEET | Excel Programming | |||
Code cleanup | Excel Programming | |||
Log File CleanUp | Excel Programming |