Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert cell contents to number
I would like to convert all the cells with contents in Column I to a number.
I am using the following macro to do so, however I am getting zeros in rows that do not have any contents in their cells. Can you tell me how to change the code below so that only the cells with contents in Column I will be converted to numbers? Sub Macro5() Columns("J:J").Select Selection.Insert Shift:=xlToRight Range("J2").Select ActiveCell.FormulaR1C1 = "1" Selection.Copy Range("I2:I100").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Columns("J:J").Delete End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert cell contents to number
Sub Macro5()
Columns("J:J").Select Selection.Insert Shift:=xlToRight Range("J2").Select ActiveCell.FormulaR1C1 = "1" For Each r In Range("I2:I100") If Not IsEmpty(r) Then Range("J2").Copy r.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End If Next Columns("J:J").Delete End Sub -- Gary''s Student - gsnu200909 "Jazz" wrote: I would like to convert all the cells with contents in Column I to a number. I am using the following macro to do so, however I am getting zeros in rows that do not have any contents in their cells. Can you tell me how to change the code below so that only the cells with contents in Column I will be converted to numbers? Sub Macro5() Columns("J:J").Select Selection.Insert Shift:=xlToRight Range("J2").Select ActiveCell.FormulaR1C1 = "1" Selection.Copy Range("I2:I100").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Columns("J:J").Delete End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert cell contents to number
Use this method to only apply the multiplication to cells with values in them:
========= Sub Macro5() Columns("J:J").Insert Shift:=xlToRight Range("J2") = 1 Range("J2").Copy Columns("I:I").SpecialCells(xlCellTypeConstants, 23).PasteSpecial _ Paste:=xlPasteAll, Operation:=xlMultiply Columns("J:J").Delete End Sub ========= Does that help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Jazz" wrote: I would like to convert all the cells with contents in Column I to a number. I am using the following macro to do so, however I am getting zeros in rows that do not have any contents in their cells. Can you tell me how to change the code below so that only the cells with contents in Column I will be converted to numbers? Sub Macro5() Columns("J:J").Select Selection.Insert Shift:=xlToRight Range("J2").Select ActiveCell.FormulaR1C1 = "1" Selection.Copy Range("I2:I100").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Columns("J:J").Delete End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert cell contents to range | Excel Worksheet Functions | |||
How to convert cell contents | Excel Worksheet Functions | |||
How to convert cell contents? | Excel Discussion (Misc queries) | |||
How do I convert cell contents that are Hex to binary? | Excel Worksheet Functions | |||
convert cell contents into a comment | Excel Programming |