Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to convert text to number
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to convert text to number
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 "Dee Sperling" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to convert text to number
Try the below alternate method. You dont need to loop...
Columns("H") = Columns("H").value -- Jacob "Dee Sperling" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to convert text to number
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. -- Rick (MVP - Excel) "Jacob Skaria" wrote in message ... Try the below alternate method. You dont need to loop... Columns("H") = Columns("H").value -- Jacob "Dee Sperling" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text number to number formate | Excel Discussion (Misc queries) | |||
Convert a number formatted as text to a number in a macro | Excel Programming | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions | |||
Convert number in text format to number | Excel Programming |