how do i convert a number stored as text to a numb in a vba loop

[Guest]

hello,

i have a question regarding excel giving the error that the number is stored
as text.

can i convert these cells using a for loop until the last row? i tried
multiplying the cell value by 1 in a for loop but this this did not seem to
work. i still get the green triangle next the cell indicating number stored
as text.

any help is appreciated.

bobm

 

[Guest]

As you go through the for loop, change the cell's format to General. It may
be stuck as text.

 

[theDude]

Hi bobm,

You can convert data types using VBA; there are a number of functions
to accomplish what you want depending on what type of number you wish
to convert the text string form of the 'number' into. To convert it to
an Integer data type, this could work for you:


Code:
--------------------
Dim myVal as Integer
myVal = *CInt*(ActiveCell.Value)
--------------------

Refer to your Visual Basic Help topic "Type Conversion Functions" for
details on the different type conversion functions...

Hope this helps,
theDude

 

[Dave Peterson]

Another option.

Select an empty cell
copy it
select your range to fix
edit|paste special|Check Add

If you need a macro, record it when you do it once. It might be faster than
looping.

Kind of like this:

Option Explicit
Sub testme01()

Dim myHelperCell As Range
Dim myRng As Range

Set myRng = Selection 'or whatever range you want

With ActiveSheet
Set myHelperCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

With myRng
.NumberFormat = "General"
myHelperCell.Copy
.PasteSpecial Operation:=xlAdd
End With
End Sub