error convert to number macro
 

Hello all-
I am attempting to make a macro that will make a pivot table that uses a
field that is a list of management areas where some are alphanumeric(6W,9T),
some are numeric(15,16,17). When I export the data I need from our oracle
database to excel(excel5 with headers), the whole management area field comes
up as text. The numeric data comes up with excel errors the ones with the
green dog ear in the corner. To fix the error I highlight these cells and
click the exclamation point icon that appears, and select the option to
convert text to number. That solves the problem. I want to record a macro
to accomplish this. If anyone knows how to do this. I need to know, I'm
fairly versed in VB if anyone has any code for this. Thank you!!!

error convert to number macro
 

Versed or not, you could begin recording a macro before attempting to convert
one cell. After that, you can use the resulting code to move from cell to
cell, and do a comparison of the cell to see if it is fully numeric. (I say
fully, because I can not remember if the function that tests if it is numeric
stops at the first character, or if it looks at the whole piece of data.) If
the full text of the cell is numeric then do the conversion that was recorded
before. If not, then go to the next cell that is to be "tested".

You should be able to construct what you need/want from that.

"cherrynich" wrote:

Hello all-
I am attempting to make a macro that will make a pivot table that uses a
field that is a list of management areas where some are alphanumeric(6W,9T),
some are numeric(15,16,17). When I export the data I need from our oracle
database to excel(excel5 with headers), the whole management area field comes
up as text. The numeric data comes up with excel errors the ones with the
green dog ear in the corner. To fix the error I highlight these cells and
click the exclamation point icon that appears, and select the option to
convert text to number. That solves the problem. I want to record a macro
to accomplish this. If anyone knows how to do this. I need to know, I'm
fairly versed in VB if anyone has any code for this. Thank you!!!

error convert to number macro
 

I tried that before I posted. You start recording and then convert one, then
stop recording and look at the code, and there isn't any code. Thank you
though.

error convert to number macro
 

Well even so, I just took a blank cell, recorded a macro of setting the
Selection.NumberFormat = "0.00"

What I would do is loop through each cell in the desired range, if the text
of the cell isnumeric then select the cell and change the numberformat to the
desired format.
Ie.
For I = StartRow to EndRow
If IsNumeric(Application.Cells(I, ColumnLocation).Text) = true then
Application.Cells(I, ColumnLocation).NumberFormat = "0.00"
end if
Next I

I also did a little investigation about ensuring that the data in the cell
is actually numeric. If in the import it prepends the data with an
apostrophe, you can use the RIGHT function to work only with the portion of
the string that may or may not be numeric. You might also have to reset the
cells.value to your newly discovered "number" instead of the text present.

Some ideas, not perhaps a final answer, but somewhere to work from.
Maybe someone else can help you further?

Of course substituting
"cherrynich" wrote:

I tried that before I posted. You start recording and then convert one, then
stop recording and look at the code, and there isn't any code. Thank you
though.