Convert cell contents to number


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
 
Ad

Advertisements

G

Gary''s Student

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
 
Ad

Advertisements

J

JBeaucaire

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top