| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Active Cells
Hi everyone,
How can I format the following please :- Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik <--- Format Right .Offset(1, 1).Value = Tested <--- Accept Thousands & NO Decimal Places .Offset(1, 2).Value = tly <--- Accept Thousands & NO Decimal Places .Offset(1, 3).Value = 100 / Tested * tly <--- 5 Decimal Places .Offset(1, 4).Value = Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) <--- 5 Decimal Places .Offset(1, 6).Value = tly + Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) <--- General & NO Decimal Places .Offset(1, 0).Select End With Thanks in Advance. All the Best. Paul |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting Active Cells
Hi Paul,
I'm not sure what you mean by accept 1000's and no decimals. I have assumed you mean use the comma to separate as per 1,000. However, here is an example of formatting integrated into you code. If the formats are not exactly as you want then simply record a macro as you set the required formats and you will be able to get the syntax to edit the example. There is no general format with no decimals. General format will include decimals if they are there or leave them out if not there. Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik .Offset(1, 0).HorizontalAlignment = xlRight .Offset(1, 1).Value = Tested .Offset(1, 1).NumberFormat = "#,##0" .Offset(1, 2).Value = tly .Offset(1, 2).NumberFormat = "#,##0" .Offset(1, 3).Value = 100 / Tested * tly .Offset(1, 3).NumberFormat = "0.00000" .Offset(1, 4).Value = Tested - tly .Offset(1, 4).NumberFormat = "#,##0" .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) .Offset(1, 5).NumberFormat = "0.00000" .Offset(1, 6).Value = tly + Tested - tly .Offset(1, 6).NumberFormat = "#,##0" .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) .Offset(1, 7).NumberFormat = "General" .Offset(1, 0).Select End With Regards, OssieMac "Paul Black" wrote: Hi everyone, How can I format the following please :- Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik <--- Format Right .Offset(1, 1).Value = Tested <--- Accept Thousands & NO Decimal Places .Offset(1, 2).Value = tly <--- Accept Thousands & NO Decimal Places .Offset(1, 3).Value = 100 / Tested * tly <--- 5 Decimal Places .Offset(1, 4).Value = Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) <--- 5 Decimal Places .Offset(1, 6).Value = tly + Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) <--- General & NO Decimal Places .Offset(1, 0).Select End With Thanks in Advance. All the Best. Paul |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting Active Cells
Thanks OssieMac,
That is EXACTLY what I was after. Thanks Again. All the Best. Paul On Aug 23, 12:20 pm, OssieMac wrote: Hi Paul, I'm not sure what you mean by accept 1000's and no decimals. I have assumed you mean use the comma to separate as per 1,000. However, here is an example of formatting integrated into you code. If the formats are not exactly as you want then simply record a macro as you set the required formats and you will be able to get the syntax to edit the example. There is no general format with no decimals. General format will include decimals if they are there or leave them out if not there. Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik .Offset(1, 0).HorizontalAlignment = xlRight .Offset(1, 1).Value = Tested .Offset(1, 1).NumberFormat = "#,##0" .Offset(1, 2).Value = tly .Offset(1, 2).NumberFormat = "#,##0" .Offset(1, 3).Value = 100 / Tested * tly .Offset(1, 3).NumberFormat = "0.00000" .Offset(1, 4).Value = Tested - tly .Offset(1, 4).NumberFormat = "#,##0" .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) .Offset(1, 5).NumberFormat = "0.00000" .Offset(1, 6).Value = tly + Tested - tly .Offset(1, 6).NumberFormat = "#,##0" .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) .Offset(1, 7).NumberFormat = "General" .Offset(1, 0).Select End With Regards, OssieMac "Paul Black" wrote: Hi everyone, How can I format the following please :- Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik <--- Format Right .Offset(1, 1).Value = Tested <--- Accept Thousands & NO Decimal Places .Offset(1, 2).Value = tly <--- Accept Thousands & NO Decimal Places .Offset(1, 3).Value = 100 / Tested * tly <--- 5 Decimal Places .Offset(1, 4).Value = Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) <--- 5 Decimal Places .Offset(1, 6).Value = tly + Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) <--- General & NO Decimal Places .Offset(1, 0).Select End With Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting Active Cells
Hi OssieMac,
One last point please. What about if I wanted to format ... ..Offset(0, 0).Value = "For 1 - " & P & " there are " & tly & " different sets of " & cmb & " numbers. " .... as "#,##0" for the & tly & output please?. Thanks in Advance. All the Best. Paul On Aug 23, 12:31 pm, Paul Black wrote: Thanks OssieMac, That is EXACTLY what I was after. Thanks Again. All the Best. Paul On Aug 23, 12:20 pm, OssieMac wrote: Hi Paul, I'm not sure what you mean by accept 1000's and no decimals. I have assumed you mean use the comma to separate as per 1,000. However, here is an example of formatting integrated into you code. If the formats are not exactly as you want then simply record a macro as you set the required formats and you will be able to get the syntax to edit the example. There is no general format with no decimals. General format will include decimals if they are there or leave them out if not there. Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik .Offset(1, 0).HorizontalAlignment = xlRight .Offset(1, 1).Value = Tested .Offset(1, 1).NumberFormat = "#,##0" .Offset(1, 2).Value = tly .Offset(1, 2).NumberFormat = "#,##0" .Offset(1, 3).Value = 100 / Tested * tly .Offset(1, 3).NumberFormat = "0.00000" .Offset(1, 4).Value = Tested - tly .Offset(1, 4).NumberFormat = "#,##0" .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) .Offset(1, 5).NumberFormat = "0.00000" .Offset(1, 6).Value = tly + Tested - tly .Offset(1, 6).NumberFormat = "#,##0" .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) .Offset(1, 7).NumberFormat = "General" .Offset(1, 0).Select End With Regards, OssieMac "Paul Black" wrote: Hi everyone, How can I format the following please :- Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik <--- Format Right .Offset(1, 1).Value = Tested <--- Accept Thousands & NO Decimal Places .Offset(1, 2).Value = tly <--- Accept Thousands & NO Decimal Places .Offset(1, 3).Value = 100 / Tested * tly <--- 5 Decimal Places .Offset(1, 4).Value = Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) <--- 5 Decimal Places .Offset(1, 6).Value = tly + Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) <--- General & NO Decimal Places .Offset(1, 0).Select End With Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting Active Cells
Hi again Paul,
If I understand correctly you are putting a string together by concatenation. When you do this the whole lot becomes a string and while formatting can be done in one line of code with nested formulas I think that it is better to create new formatted string values before concatenating for the output. The following is an example only and you will have to work through it and edit your code accordingly. I only did a couple of your variables as examples. The format between the double quotes can be any format that you can find in the dialog box including custom formatting in the usual manual formatting of cells on the worksheet. You can even create a string value from a date variable to return only month and year like this:- dateString = format(datevariable,"mmm yyyy") Give it a try and get back to me if still having a problem. However, if you have to get back to me on it then give me an example of what each of your variables contains and how you want the output to appear such as:- P = 5.8674988574 to appear as 5.86750. Dim P As Single 'Numeric value Dim Pstr As String 'String value Dim tly As Single 'Numeric value Dim tlyStr As String 'String value P = 1001.85473568 'In numeric format Pstr = Format(numericValue, "0.00000") 'In string format tly = 1000000 'In numeric format tlyStr = Format(tly, "#,##0") 'In string format 'In your code replace the numeric variables with the 'formatted text variables With ActiveCell ..Offset(0, 0).Value = "For 1 - " & Pstr & " there are " _ & tlyStr & " different sets of " & cmb & " numbers. " End With Regards, OssieMac "Paul Black" wrote: Hi OssieMac, One last point please. What about if I wanted to format ... ..Offset(0, 0).Value = "For 1 - " & P & " there are " & tly & " different sets of " & cmb & " numbers. " .... as "#,##0" for the & tly & output please?. Thanks in Advance. All the Best. Paul On Aug 23, 12:31 pm, Paul Black wrote: Thanks OssieMac, That is EXACTLY what I was after. Thanks Again. All the Best. Paul On Aug 23, 12:20 pm, OssieMac wrote: Hi Paul, I'm not sure what you mean by accept 1000's and no decimals. I have assumed you mean use the comma to separate as per 1,000. However, here is an example of formatting integrated into you code. If the formats are not exactly as you want then simply record a macro as you set the required formats and you will be able to get the syntax to edit the example. There is no general format with no decimals. General format will include decimals if they are there or leave them out if not there. Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik .Offset(1, 0).HorizontalAlignment = xlRight .Offset(1, 1).Value = Tested .Offset(1, 1).NumberFormat = "#,##0" .Offset(1, 2).Value = tly .Offset(1, 2).NumberFormat = "#,##0" .Offset(1, 3).Value = 100 / Tested * tly .Offset(1, 3).NumberFormat = "0.00000" .Offset(1, 4).Value = Tested - tly .Offset(1, 4).NumberFormat = "#,##0" .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) .Offset(1, 5).NumberFormat = "0.00000" .Offset(1, 6).Value = tly + Tested - tly .Offset(1, 6).NumberFormat = "#,##0" .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) .Offset(1, 7).NumberFormat = "General" .Offset(1, 0).Select End With Regards, OssieMac "Paul Black" wrote: Hi everyone, How can I format the following please :- Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik <--- Format Right .Offset(1, 1).Value = Tested <--- Accept Thousands & NO Decimal Places .Offset(1, 2).Value = tly <--- Accept Thousands & NO Decimal Places .Offset(1, 3).Value = 100 / Tested * tly <--- 5 Decimal Places .Offset(1, 4).Value = Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) <--- 5 Decimal Places .Offset(1, 6).Value = tly + Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) <--- General & NO Decimal Places .Offset(1, 0).Select End With Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting Active Cells
Thanks OssieMac, I appreciate it.
All the Best. Paul On Aug 23, 1:46 pm, OssieMac wrote: Hi again Paul, If I understand correctly you are putting a string together by concatenation. When you do this the whole lot becomes a string and while formatting can be done in one line of code with nested formulas I think that it is better to create new formatted string values before concatenating for the output. The following is an example only and you will have to work through it and edit your code accordingly. I only did a couple of your variables as examples. The format between the double quotes can be any format that you can find in the dialog box including custom formatting in the usual manual formatting of cells on the worksheet. You can even create a string value from a date variable to return only month and year like this:- dateString = format(datevariable,"mmm yyyy") Give it a try and get back to me if still having a problem. However, if you have to get back to me on it then give me an example of what each of your variables contains and how you want the output to appear such as:- P = 5.8674988574 to appear as 5.86750. Dim P As Single 'Numeric value Dim Pstr As String 'String value Dim tly As Single 'Numeric value Dim tlyStr As String 'String value P = 1001.85473568 'In numeric format Pstr = Format(numericValue, "0.00000") 'In string format tly = 1000000 'In numeric format tlyStr = Format(tly, "#,##0") 'In string format 'In your code replace the numeric variables with the 'formatted text variables With ActiveCell .Offset(0, 0).Value = "For 1 - " & Pstr & " there are " _ & tlyStr & " different sets of " & cmb & " numbers. " End With Regards, OssieMac "Paul Black" wrote: Hi OssieMac, One last point please. What about if I wanted to format ... ..Offset(0, 0).Value = "For 1 - " & P & " there are " & tly & " different sets of " & cmb & " numbers. " .... as "#,##0" for the & tly & output please?. Thanks in Advance. All the Best. Paul On Aug 23, 12:31 pm, Paul Black wrote: Thanks OssieMac, That is EXACTLY what I was after. Thanks Again. All the Best. Paul On Aug 23, 12:20 pm, OssieMac wrote: Hi Paul, I'm not sure what you mean by accept 1000's and no decimals. I have assumed you mean use the comma to separate as per 1,000. However, here is an example of formatting integrated into you code. If the formats are not exactly as you want then simply record a macro as you set the required formats and you will be able to get the syntax to edit the example. There is no general format with no decimals. General format will include decimals if they are there or leave them out if not there. Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik .Offset(1, 0).HorizontalAlignment = xlRight .Offset(1, 1).Value = Tested .Offset(1, 1).NumberFormat = "#,##0" .Offset(1, 2).Value = tly .Offset(1, 2).NumberFormat = "#,##0" .Offset(1, 3).Value = 100 / Tested * tly .Offset(1, 3).NumberFormat = "0.00000" .Offset(1, 4).Value = Tested - tly .Offset(1, 4).NumberFormat = "#,##0" .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) .Offset(1, 5).NumberFormat = "0.00000" .Offset(1, 6).Value = tly + Tested - tly .Offset(1, 6).NumberFormat = "#,##0" .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) .Offset(1, 7).NumberFormat = "General" .Offset(1, 0).Select End With Regards, OssieMac "Paul Black" wrote: Hi everyone, How can I format the following please :- Worksheets("Test").Select With ActiveCell .Offset(1, 0).Value = cmb & " If " & pik <--- Format Right .Offset(1, 1).Value = Tested <--- Accept Thousands & NO Decimal Places .Offset(1, 2).Value = tly <--- Accept Thousands & NO Decimal Places .Offset(1, 3).Value = 100 / Tested * tly <--- 5 Decimal Places .Offset(1, 4).Value = Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 5).Value = (100 / Tested) * (Tested - tly) <--- 5 Decimal Places .Offset(1, 6).Value = tly + Tested - tly <--- Accept Thousands & NO Decimal Places .Offset(1, 7).Value = (100 / Tested * tly) + ((100 / Tested) * (Tested - tly)) <--- General & NO Decimal Places .Offset(1, 0).Select End With Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting of Entire Row when the Active Cell is in ro | Excel Discussion (Misc queries) | |||
| Conditional Formatting in ref to active cell | Excel Worksheet Functions | |||
| How can I have formatting options like merge cells ,Bold,active for the unlocked cells of the protected worksheet.Is it possible in excel? | Excel Programming | |||
| How can I have formatting toolbar active for the unprotected(unlocked) cells of the protected worksheet??? | Excel Discussion (Misc queries) | |||
| Change Formatting In 'Active' Cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
