Convert range of text to number in VBA?

[SwordMonkey]

I'm importing some ID's from an Oracle DB into Excel. They come over alright, but when I go into the spreadsheet, there's a little ! next to the cells and says "Number stored as Text" I can then manually convert these to Numbers which fixes the problem. However, I'd like to know if there's an easy way to do this in the VBA, say at the end of the data pull? Just convert the whole range to numbers all at once.

Thanks for any help.

 

[TLady]

Try this
Select column that will get populatd with those ! records



In XL go to Format Cell and pick "Number"
Adjust decimals etc

TIA

 

[SwordMonkey]

Thanks for the reply TLady. Unfortunately that way only worked until I repulled the data.

But your comments made me come up with a solution that I of course couldn't think of until now. Just did a CInt() on the numbers as they came over.

 

[MJoyce]

Is there a way I can click a button to change any text in a selected range to lowercases ?

I suppose what I am asking is, how do I referece selected cells in vba ?

thnaks

 

[xlbo]

MJoyce - please don't piggy back on threads - start a new thread.
Answer is use SELECTION to relate to selected cells
If you select a range, this'll change it all to lowercase

for each c in selection
c.value = lcase(c.value)
next

will change formulae to values tho so be careful

extended syntax would be:
for each c in selection
if c.value = c.formula then
c.value = lcase(c.value)
else
end if
next

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes