Convert cell contents to number

  • Thread starter Thread starter Jazz
  • Start date Start date
J

Jazz

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
 
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?
 
Back
Top