Converting number to text

[jazzyt2u]

Hi,
I'm trying to convert numbers to text so that after rounding a number if it's even I want the number to have a .0

I tried using the following code:



ActiveCell = Round(ActiveCell, 1)
ActiveCell = Text(ActiveCell, ".0")
DwnLdThurs = ActiveCell



The code fails because of the Text function.

Help Please....

[GTO]

Hi Jazzy,

Not sure, but maybe?

Sub ex()
Dim DwnLdThurs As String
ActiveCell.Value = Round(ActiveCell.Value, 1)
ActiveCell.NumberFormat = "#.0"
DwnLdThurs = Format(ActiveCell.Value, "#.0")
End Sub


Hope this helps,

Mark

[Ken Puls]

To do it over a whole range, I'd probably go with something like:
Sub ex1()
Dim cl As Range

Application.ScreenUpdating = False
For Each cl In Selection
cl.Value = Round(cl.Value, 1)
Next cl

Selection.NumberFormat = "#.0"
Application.ScreenUpdating = True
End Sub

Mark, why the first and last lines in yours? They don't seem to do anything necessary to me. :dunno

[GTO]

@Ken Puls:

Howdy Ken :hi: How's you and yours?

I am afraid I was guessing a bit as to rounding to tenths in the cell. As to the variable, it seemed that maybe this was the goal? I sorta figured Jazzy might say where the variable is going...

Mark

[Ken Puls]

Howdy Ken :hi: How's you and yours?

Good thanks. They keep me too busy to show up much though. :( I'm feeling like I'm suffering from forum deprivation.

[jazzyt2u]

Hi,

Thank you both for your assistance.
The variable is going to be find/replace in word and it was leaving the zero off after (I don't know the correct verbiage) but when I


DwnLdThurs = Activecell

Even though I formatted it the way I wanted. What I came up with was:


Sub ex1()
ActiveCell = Round(ActiveCell / 1000, 1)
ActiveCell = "=Text(" & ActiveCell & "," & """.0"")"
DwnLdThurs = Activecell & "K"
End Sub

so i can get 5.0K or 5.5K

[GTO]

Hi Jazzy,

Glad you got 'er solved. Just thought to mention that if what's displayed in the cell isn't important, you could try:

Option Explicit

Sub ex()
Dim DwnLdThurs As String
DwnLdThurs = Format(Round(ActiveCell.Value, 1), "#.0") & "K"
End Sub


@Ken Puls:


Good thanks. They keep me too busy to show up much though. :( I'm feeling like I'm suffering from forum deprivation.

Not exactly related, but this reminded me to say that I would like to relay my appreciation to all of you. I just saw (a few days ago) that the KB is back at 100%. Very Cool Amundo!

(Malcom, your workaround in the meantime was just plain great!)

A great day to all,

Mark

[Ken Puls]

I'm not clear on something...
-Are you trying to set the variable so that you can use it in another function?
-Are you trying to change what is displayed in the cell?
-Are you trying to do both?

Your current routine is doing both, and converting your numbers into text. This may be good for Word, but you'll lose their number functionality in Excel.

If you just want to use the number in another routine, ignore you may want to think about avoiding the variable and using a UDF:
Function FormatAS(rng As Range) As String
FormatAS = Format(Round(rng / 1000, 1), "#.0K")
End Function
You'd then use this in another procedure like this:
DwnLdThurs = FormatAS(activecell)

The nice thing about the UDF is that you can use this from any of your code and it doesn't modify the source data.

If you're just trying to do it in the cell, however, you could just set a custom number format on the cell. This wouldn't change the value at all, but would change the way it displays.