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

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
>

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
>