Problems with datatypes 1

[cmz21]

Hello,
I am trying to use vba to create an excel spreadsheet. The problem I am having revolves around 1 column. Some of the values in the column are numeric (eg, 1). There are a few with a value 09, which are coming in as text. I would like to do automation with vba, but I can't get these values converted to the same datatype.

I cannot even get the data into an access table because of these values.

I don't understand how this could have happened - I recieved the worksheet and was asked to do these things with it.

Any thoughts on what I can do about this aside from manually making the changes?

 

[AnotherHiggins]

If you are using a macro, do this - replacing column A with whatever column is giving you trouble:

range("A:A").value = range("A:A").value

The cause of the problem is that if the Excel cell reads 09 when you get it, either the cell is using a custom format to force it to display two digits or, as is evidently the case here, it is formatted as text. You can replicate this by entering '09. That single quote at the begining tells Excel to treat the cell as text.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

Oh yeah, if you ever run across this problem and you are not using VBA, you can accomplish the same thing by using the 'times one fix'. It goes like this:
[ul][li]Enter 1 into an empty cell in a column to the right of all of your data
(This should automatically right-align - that indicates that Excel does recognize this as a number)[/li]
[li]Copy that cell[/li]
[li]Highlight the range you want to convert to numbers[/li]
[li]Edit > Paste Special > Multiply[/li][/ul]
NOTE: Any empty cells that are highlighted will be converted to zeros

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[cmz21]

Yes, but how can I get the text cell 09 to be interpreted as number value without the 0. I want to perform grouping on the column, but it won't group 9 with 09. So even if I change the 9 number format cells to a text, they still will not be grouped together because of the 0.

 

[AnotherHiggins]

Have you tried either of the solutions I've offered? They will both change the '09' (text) to '9' (number).

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[cmz21]

I tried using

range("A:A").value = range("A:A").value

in my vba, but that didn't seem to do anything.

I will try your second solution.

Thanks

 

[cmz21]

I have tried your second suggestion and it seemed to have worked. I have been able to transfer this into vba and it is working fine.

Thanks for your help.

 

[AnotherHiggins]

Not sure why the first one didn't work - it's always worked for me. But I'm glad you got it sorted.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.