Trying to convert text to number

D

Dee Sperling

I'm trying to convert all numbers in Column H from text to number. Some
numbers are text (with an apostrophe at the beginning) and some are just
plain number.
If I put a 1 in an empty cell and manual do a Copy/Paste Special/Multiply,
it works.

This is what I wrote:
Sub ConvertPINtoNum()
Range("H2").Select
RowCount = 2
Do While Range("F" & (RowCount)) <> ""
Range("H" & (RowCount)) = ("H" & (RowCount)) * 1
RowCount = RowCount + 1
Loop
End Sub

I'm getting a type mismatch error on the line
Range("H" & (RowCount)) = ("H" & (RowCount)) * 1
I also tried
but I'm getting a type mismatch error on the line
Range("H" & (RowCount)) = ("H" & (RowCount)) * 1
but that gave me the type mismatch error as well.

Any help very much appreciated.

Thanks,
Dee
 
J

Jacob Skaria

Syntax error....Try the below

Sub ConvertPINtoNum()
RowCount = 2
Do While Range("F" & RowCount) <> ""
Range("H" & RowCount) = Range("H" & RowCount) * 1
RowCount = RowCount + 1
Loop
End Sub
 
J

Jacob Skaria

Try the below alternate method. You dont need to loop...

Columns("H") = Columns("H").value
 
R

Rick Rothstein

You might need to convert the cell format to "General" before your statement
to handle the cells currently formatted as Text...

Columns("H").NumberFormat = "General"
Columns("H").Value = Columns("H").Value

Note to Dee... The technique of assigning the Value property back onto
itself should only be used on cells containing constant value; if the cell
has a formula in it, that formula would be replaced by the value it is
displaying. You can get around that problem by assigning the Formula
property back onto itself instead...

Columns("H").NumberFormat = "General"
Columns("H").Formula= Columns("H").Formula

You can use this on both cells containing formulas and constants and only
the constants will be changed... the formulas will remain as formulas.
 

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