Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote: 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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote: 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" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote in message ... 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" wrote: 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" wrote: 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex Formatting of cell
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional formatting | Excel Worksheet Functions | |||
Formula help for complex formatting. | Excel Worksheet Functions | |||
complex?? Q about Conditional formatting | Excel Worksheet Functions | |||
Complex formatting help | Excel Programming | |||
complex cell formatting using VBA | Excel Programming |