How to convert any types format into Text with 5 digits in selection?
For instance, the content I select is "234", and I want it to convert to "00234". Just like the function "TEXT" in excel. How can I realize it promptly in VBA?
Thanks!!!
How to convert any types format into Text with 5 digits in selection?
For instance, the content I select is "234", and I want it to convert to "00234". Just like the function "TEXT" in excel. How can I realize it promptly in VBA?
Thanks!!!
VBA's Format function works the same as TEXT.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
but I tryit doesn't work.Option Explicit Sub text() Range("a1") = WorksheetFunction.text(Range("a1"), "00000") End Sub
Perhaps
With Range("a1") .NumberFormat = "@" .Value = Format$(Range("a1"), "0000") End With
Last edited by jindon; 08-19-2014 at 08:52 PM.
http://i.hizliresim.com/X2pkBD.jpg
TextBox2 = Format(TextBox1, "00000")
deleted......
@jindon what has happened??
Thank you guys!
@jindon your code is what I exactly want! But how does it work? Why use "@" and why put a $ after Fomat??
However, another problem shows up, it doesn't work when I choose a range.
With Selection .NumberFormat = "@" .Value = Format$(Selection, "00000") End With
Last edited by qzqzjcjp; 08-19-2014 at 09:23 PM.
Perhaps
Range("A1").Select
Selection.NumberFormat = "00000"
Hello
selection.JPG
Select worksheet-> selection change event and write the code, there
Regards,With Selection
.NumberFormat = "@"
.Value = Format$(Selection, "00000")
End With
It still doesn't work. The error is always: Run-time error '13' Type mismatch. I modified the code a little bit and it works!!!
But another problem comes up, If I select the whole column or whole cells, it costs too much time to run.Option Explicit Sub ConvertNumber2Text() Dim currrange As Range Dim cell As Object With selection .NumberFormat = "@" End With Set currrange = selection For Each cell In currrange cell.Value = Format$(cell.Value, "00000") Next cell End Sub
Last edited by qzqzjcjp; 08-19-2014 at 10:46 PM.
Change
toSet currrange = selection
Set currrange = selection.specialcells(2,1)
Thanks!!! Everyone!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks