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

[bobm]

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

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

Please Login or Register  to view this content.

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

Hope this helps,
theDude

[Gary's Student]

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


"bobm" wrote:

> 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

[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


bobm wrote:
>
> 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

--

Dave Peterson

[Newbie_Nick]

Dave, I tried your method - it works when I do it manually but somehow doesn't function when I record a macro or use your code.

It's absurd and it's driving me insane.
Any suggetstions?

Thanks