easy for most of you

[dorjee]

Hi! I just need to do :
I have a sheet column with numbers formatted as text. I need to format them as numbers and put the max value into an Integer variable on my vba code. I don t know the vba syntax to use these functions...
help..

 

[ETID]

Assuming that the numbers as text are in col A
and that your sheet has nothing in cell AA1


Range("aa1&quot = 1
Range("AA1&quot.Select
Selection.Copy
Columns("A:A&quot.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

x = Application.WorksheetFunction.Max(Range("a:a&quot)

Range("aa1&quot = ""

End Sub

 

[dorjee]

hi, thanks for the response..
I am not sure I understand this solution:
what i see is that We are copying from aa1 to A. But I dont see where the conversion takes place. Could you please be very kind and comment a little for me?
thanks a lot

 

[KenWright]

What's happening is that by copying a 1 and the multiplying the data in that column with the 1 you effectively coerce the data back to numeric. the fact that it is a 1 means the data doesn't change value.

You can also do this manually, or even simply copy an empty cell, select the data and do Edit / Paste Special / Add, which again will add 0 to each of the values, and in doing so will coerce Excel into understanding that the data is to be treated as numeric. It then does the conversion by itself.

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dorjee]

isnt it possible that this code you wrote gives a run time error 9(because of the range..)?

 

[ETID]

It works with no error on my machine....

do you have some sheet protection active?