Trying to convert text to number

[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

 

[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

 

[Jacob Skaria]

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

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

 

[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.