• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number Formatting

S

srikandi2000

Member
Dear All


Have anyone tried scaling in number format using VBA function FORMAT...


I tried to make 18.450 into 18.5 K using z = format(z,"#.#0, ""K""") but failed... the best thing i can achieve is 18 K. I tried the same formatting in worksheet using text function and it works just fine.


Can someone point out what I did wrong?


Thanks in advance
 
Srikandi2000


Text in a shape can only refer directly to ca cell not use a formula

Use Narayan's formula in another cell and then link directly to that cell from the shape
 
Hi Srikandi ,


I do not know what you are referring to ; let me tell you what I did.


I inserted a rectangle shape in a worksheet ; the name is Rectangle 1.


I used this VBA statement :


Activesheet.Shapes("Rectangle 1").TextFrame2.TextRange.Text = Format(18.770,"#.0 K")


The rectangle displayed 18.8 K.


Narayan
 
Thanks Hui & Narayank991. Sorry I had to leave yesterday


That is strange. What I did was


z = format(k,"#.0 K")

Activesheet.Shapes("Rectangle 1").TextFrame2.TextRange.Text = z


So far, I have succeded in other formating, what is left is this one.


(I cannot reach my laptop right now, I'll check later)
 
Srikandi


That works provided k is defined as a number or variant data type
 
Hi Hui,


I been wondering about that. k is variant data type. I used

k = Sheets("analyze").Range("x46").Value


When I highlight k using STEP is shows "18450,0". I think this is the problem.


Any idea why? I tried changing k into Long type but it is still the same. here is part of the code..


Sub change_color()

Application.ScreenUpdating = False


Dim x, y, z, zz, aa

Dim k As Long

x = Sheets("Indonesia").Range("L69").Value


For i = 2 To 34

y = Sheets("dapur").Range(x & i).Value

k = Sheets("dapur").Range(x & i + 46).Value

Select Case Sheets("Indonesia").Range("K69").Value

Case 2

z = Format(k, "#.0 K")

Case 3

z = Format(k, "#,##0.00")

Case 4

z = Format(k, "#,##0.00%")

Case 5

z = Format(k, "#,##0.00")

Case Else

z = Format(k, "#,##0.00%")

End Select

zz = Sheets("dapur").Range("Y" & i + 46).Value

Sheets(1).Shapes(Range("dapur!S" & i).Value).Fill.ForeColor.RGB = Sheets(1).Range(y).Interior.Color

Sheets(1).Shapes(Range("dapur!S" & i).Value & "LN").TextFrame.Characters.Text = zz & " :" & vbCrLf & z


thanks in advance
 
Hi ,


Does it have anything to do with the decimal character ( which is the period "." in my version ) , and the thousands separator ( which is the comma "," in my version ) ?


Narayan
 
I'll upload the workbook in a moment (after i read how ^^)..

thank you for the help so far..


here is the workbook


http://sdrv.ms/Yp4gsU


the code is in "module1" and please do not try to change the "NILAI" as it will cause error since before I was using k as variant. You can change "Variabel 1"


thanks in advance
 
Srikandi2000


Firstly, This is a beautiful piece of work, You should be proud of it.


If I change Jenjang to SMP, Kalteng displays 6,648 K


Isn't that what you want ?


So I suspect that Narayan's comments above about the Localisation maybe to play here


Which combinations of Values(inputs) gives 18450.0 K on which island?
 
Hi ,


Your values in Z48 and onwards are text values , where your format is anyway removing the decimal places ! How can your VBA thereafter introduce any decimal values ?


=TEXT(T2,"#,##0")


has to be changed to preserve the decimal places , if any. It's a different matter that the values in T2 and onwards do not show any decimal values ; all are integer values.


Narayan
 
@Hui, thank you (i wish my superior had the same view as you ^^). what I like to achieve is 6.6 K and I think Narayank991 post answer that.


@Narayank991, Thank you... I haven't though of that. I'll try to revise it. It's an old file, kinda hard to remember.


Kudos to both of you.


Best regards
 
Srikandi2000


Don't forget that even though you are using VBA to format the number the Format has the syntax of


+'ve Number, -'ve Number, Zero, Text


So you are applying a Number format of say "#,### K", well that only applies to positive numbers

-'ve numbers, 0 and Text maintain there default formats


As Narayan pointed out the format in the cells is converting the numbers to text

so it bypasses the number format you are trying to apply using VBA.


To fix it either, remove the number formats from the Cells Z48..Zxx, so that the cells are still numbers, then your VBA will work as planned


I hope that helps explain what is going on
 
In Z48 it should be

=T2


Not

=TEXT(T2,"#,##0")


Copy Z48 down and across


Also which input values give you 18450.0 K on which island?

What value should it be?
 
That is exactly what I just did and I even add 1 decimal. Am I missing something?


Just to be sure... Kalteng (Guru as variable) using "#.0 K" shows 29940,0 K, it should be 29,9 K
 
Try the following number format

[pre]
Code: 
Case 2
z = Format(k, "0,.0 K")
[/pre]
This shows me Kalteng as 23.2 K


I am using a US Number system and so a decimal is . a Thousands seperator is a ,

you may have to experiment with these if your localisation is different
 
That is great Hui. It works now... The number format is 'strange'. How/Where do you come up with that?


*BTW, I change the Z48 and so on back to =TEXT(T2;"#,##0") and it still works.

Regards
 
Have a read of:

http://chandoo.org/wp/2011/11/02/a-technique-to-quickly-develop-custom-number-formats/

http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/


Including some of the included Links to other sites
 
Changing Z48 to Format(...) still works as the VBA code is taking the value of the cell not the text

Code: 
k = Sheets("dapur").Range(x & i + 46).Value


So even though Z48 is displayed as 23.2 K, internally it is still 23,200
 
My goodnes, I just realized I have asked wrongly. Narayank991 and you must assume that I'm trying to make 18 point 450 into 18 K while I meant making 18 thousand and 450 into 18 K. So sorry for that.


I just wasted both of your time.
 
I think we both understood that you were trying to display

18 Thousand 4Hundred and 50 as 18.45 K


The confusion comes due to the use of ; or , or . as delimiters
 
You must log in or register to reply here.
Back
Top