Complex Formatting of cell

G

Guest

Using the format cell dialog box one can choose Custom Format and give the
format as

"String" ###0.0

The end result is a cell which has a number and text (String) in it, but
when you click on the contents of the cell you can only see the number and
cannot see the text (as it is in the format)

However I don't want to to do this manually for each cell as the string
changes for each cell.

I'm trying to figure out how a macro could set a custom format of a cell to
something like "String from RC[-5]" ###0.0

Using something like
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell = format(Y, X)

ends up showing both the string and the number in the cell, instead of
having the string in the format and not in the cell.

How can one put define a custom number format with a variable "String" in
the format?
 
B

Bob Phillips

X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
G

Guest

from the Help file

Worksheets("Sheet1").Range("A17").NumberFormat = "General"
Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
Worksheets("Sheet1").Columns("C"). _
NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
 
G

Guest

Hi Bob...thanks for this. However there is still one problem because of which
this doesnot work.
The value of X here turns out to be "String ###0.0"....and this is not a
valid format. It has to be ""String" ###0.0"
I tried using Chr(34) instead of a " but that isn't working. This is what I
did:

X = "Chr(34) String Chr(34)" & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

Any idea how I could pass an " in the Number format?

Bob Phillips said:
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Karan said:
Using the format cell dialog box one can choose Custom Format and give the
format as

"String" ###0.0

The end result is a cell which has a number and text (String) in it, but
when you click on the contents of the cell you can only see the number and
cannot see the text (as it is in the format)

However I don't want to to do this manually for each cell as the string
changes for each cell.

I'm trying to figure out how a macro could set a custom format of a cell
to
something like "String from RC[-5]" ###0.0

Using something like
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell = format(Y, X)

ends up showing both the string and the number in the cell, instead of
having the string in the format and not in the cell.

How can one put define a custom number format with a variable "String" in
the format?
Click to expand...
Click to expand...
 
B

Bob Phillips

Does this do it

X = """String from RC[-5] """ & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
ActiveCell.NumberFormat = X


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Karan said:
Hi Bob...thanks for this. However there is still one problem because of
which
this doesnot work.
The value of X here turns out to be "String ###0.0"....and this is not a
valid format. It has to be ""String" ###0.0"
I tried using Chr(34) instead of a " but that isn't working. This is what
I
did:

X = "Chr(34) String Chr(34)" & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

Any idea how I could pass an " in the Number format?

Bob Phillips said:
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Karan said:
Using the format cell dialog box one can choose Custom Format and give
the
format as

"String" ###0.0

The end result is a cell which has a number and text (String) in it,
but
when you click on the contents of the cell you can only see the number
and
cannot see the text (as it is in the format)

However I don't want to to do this manually for each cell as the string
changes for each cell.

I'm trying to figure out how a macro could set a custom format of a
cell
to
something like "String from RC[-5]" ###0.0

Using something like
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell = format(Y, X)

ends up showing both the string and the number in the cell, instead of
having the string in the format and not in the cell.

How can one put define a custom number format with a variable "String"
in
the format?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Nopes, it doesn't.
"""String from RC[-5] """ is the same as trying to pass 3 strings.
The first one would be "", the second "String from RC[-5] " and the third
would be "" again.
I'm at my wits end!

Any further ideas?
Cheers, Karan


Bob Phillips said:
Does this do it

X = """String from RC[-5] """ & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
ActiveCell.NumberFormat = X


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Karan said:
Hi Bob...thanks for this. However there is still one problem because of
which
this doesnot work.
The value of X here turns out to be "String ###0.0"....and this is not a
valid format. It has to be ""String" ###0.0"
I tried using Chr(34) instead of a " but that isn't working. This is what
I
did:

X = "Chr(34) String Chr(34)" & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

Any idea how I could pass an " in the Number format?

Bob Phillips said:
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Using the format cell dialog box one can choose Custom Format and give
the
format as

"String" ###0.0

The end result is a cell which has a number and text (String) in it,
but
when you click on the contents of the cell you can only see the number
and
cannot see the text (as it is in the format)

However I don't want to to do this manually for each cell as the string
changes for each cell.

I'm trying to figure out how a macro could set a custom format of a
cell
to
something like "String from RC[-5]" ###0.0

Using something like
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell = format(Y, X)

ends up showing both the string and the number in the cell, instead of
having the string in the format and not in the cell.

How can one put define a custom number format with a variable "String"
in
the format?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Duke,
Thanks for the help. But this is not what I'm looking for.
I'm looking for a Macro that does the same as the following windows process:
Click on a cell and click on "Format cells", then on custom format category.
In the type the custom format is "String" ###0.0.

What I'm trying to achieve is that the "String" should be a variable. The
end result of such a format is a cell which displays a number along with a
string. However if you click on the cell, it only has a number as a value.
Hence you can add/ subtract such cells even though it has a number and a
string.

For example the string could be something like "Kisses". In such a cell if
you put a number 10, then it will show up as "10 Kisses" in the cell. You can
format many cells and then add all cells to get a sum of all kisses :D

Cheers, Karan


Duke Carey said:
from the Help file

Worksheets("Sheet1").Range("A17").NumberFormat = "General"
Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
Worksheets("Sheet1").Columns("C"). _
NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"



Karan said:
Using the format cell dialog box one can choose Custom Format and give the
format as

"String" ###0.0

The end result is a cell which has a number and text (String) in it, but
when you click on the contents of the cell you can only see the number and
cannot see the text (as it is in the format)

However I don't want to to do this manually for each cell as the string
changes for each cell.

I'm trying to figure out how a macro could set a custom format of a cell to
something like "String from RC[-5]" ###0.0

Using something like
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell = format(Y, X)

ends up showing both the string and the number in the cell, instead of
having the string in the format and not in the cell.

How can one put define a custom number format with a variable "String" in
the format?
Click to expand...
Click to expand...
 
P

Peter T

Hi Karen,

Not really sure what you are trying to do or rather what the problem is.
Does the following help -

Sub test()

[a2] = "Kisses"
[b2] = 12.345678

Set rng = Range("C2")
For Each cell In rng
s = Chr(34) & cell.Offset(, -2) & Chr(34) & " 0.000"
cell.NumberFormat = s
cell.Value = cell.Offset(, -1)
cell.Formula = "=RC[-1]"
Next

rng.Columns.AutoFit

Debug.Print Range("C2").Value ' 12.345678
Debug.Print Range("C2").Text ' Kisses 12.346

End Sub

Regards,
Peter T

Karan said:
Duke,
Thanks for the help. But this is not what I'm looking for.
I'm looking for a Macro that does the same as the following windows process:
Click on a cell and click on "Format cells", then on custom format category.
In the type the custom format is "String" ###0.0.

What I'm trying to achieve is that the "String" should be a variable. The
end result of such a format is a cell which displays a number along with a
string. However if you click on the cell, it only has a number as a value.
Hence you can add/ subtract such cells even though it has a number and a
string.

For example the string could be something like "Kisses". In such a cell if
you put a number 10, then it will show up as "10 Kisses" in the cell. You can
format many cells and then add all cells to get a sum of all kisses :D

Cheers, Karan


Duke Carey said:
from the Help file

Worksheets("Sheet1").Range("A17").NumberFormat = "General"
Worksheets("Sheet1").Rows(1).NumberFormat = "hh:mm:ss"
Worksheets("Sheet1").Columns("C"). _
NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"



Karan said:
Using the format cell dialog box one can choose Custom Format and give the
format as

"String" ###0.0

The end result is a cell which has a number and text (String) in it, but
when you click on the contents of the cell you can only see the number and
cannot see the text (as it is in the format)

However I don't want to to do this manually for each cell as the string
changes for each cell.

I'm trying to figure out how a macro could set a custom format of a cell to
something like "String from RC[-5]" ###0.0

Using something like
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell = format(Y, X)

ends up showing both the string and the number in the cell, instead of
having the string in the format and not in the cell.

How can one put define a custom number format with a variable "String" in
the format?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

ALRIGHTY! Managed it finally by using the following:

Dim A As String

ActiveCell.Formula = "=RC[-5]"

A = ActiveCell.Offset(0, -11).Value

ActiveCell.NumberFormat = """" & A & """" & " ###0.0"


Thanks for all the help.

Cheers, Karan

Bob Phillips said:
Does this do it

X = """String from RC[-5] """ & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
ActiveCell.NumberFormat = X


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Karan said:
Hi Bob...thanks for this. However there is still one problem because of
which
this doesnot work.
The value of X here turns out to be "String ###0.0"....and this is not a
valid format. It has to be ""String" ###0.0"
I tried using Chr(34) instead of a " but that isn't working. This is what
I
did:

X = "Chr(34) String Chr(34)" & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

Any idea how I could pass an " in the Number format?

Bob Phillips said:
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell.Formula = Y
Activecell.NumberFormat = X

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Using the format cell dialog box one can choose Custom Format and give
the
format as

"String" ###0.0

The end result is a cell which has a number and text (String) in it,
but
when you click on the contents of the cell you can only see the number
and
cannot see the text (as it is in the format)

However I don't want to to do this manually for each cell as the string
changes for each cell.

I'm trying to figure out how a macro could set a custom format of a
cell
to
something like "String from RC[-5]" ###0.0

Using something like
X = "String from RC[-5] " & " ###0.0"
Y = "=RC[-11]"
ActiveCell = format(Y, X)

ends up showing both the string and the number in the cell, instead of
having the string in the format and not in the cell.

How can one put define a custom number format with a variable "String"
in
the format?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

capture cell number format with VBA 3
Formatting short times 2
How to retrieve number of visible digits after decimal point in VBA 5
Adding a decimal place 2
find and scroll to cell string value in Excel 2
Reverse color numeric formatting? 2
Custom cell format 1
Dynamic Offset from an Application.Goto cell = Date or Time 10

Top