Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Merging Info in Two Cells
I did get a partial response, thanks Barb, but now I need to dig into
VBA and I stumble. I guess the formatting needs to be done in VBA and the easiest way is by copying each cell and pasting the values (otherwise, I am not sure if I can do partial formatting of a cell, i.e. superscripting parts of it). So the code would read: Range("c5:g5").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveCell.Characters(Start:=4, Length:=9).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = True .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 23 End With The Problem is the Range. For Copying:Pasting, I can do such a range but not for the formatting, i.e. I would need an individual range for each cell. How do I do that in a loop command, i.e. something like dim intCol as integer for intCol = 1 to 10 if intCol <= 10 then ----- all that formatting ---- end if next intCol This does not work for whatever reason, is anybody able to help out? Thanks Katsche Hi Group, I have a question on using VBA for merging information in cells and changing the format. We do have a printout from a tabulation program (WinCross) which looks the following: Blue Green Yellow (A) (B) (C) 46.5% 35.4% 43.0% Bc AC aB That means, Blue (46.5%) is significantly different at the 95% level from Green and at the 90% level from Yellow This is spit out for about 100 tables. We now want to merge some of that info and also have the format changed, i.e. Blue Green Yellow 47%(B) 35%(A,C) 43%(B) In words: we need the percentage without the decimals and then the capital letters in parentheses, separated with a comma. To make things worse, it should also have the (A,C) et al. in superscript. Is there a way how I can do that in VBA? I have a rough idea how to get the two cells together, but I stumble over three things: 1) When I merge the cells, I get endless decimals, i.e. ..4653988798798(B) for the first column. 2) I would need nothing if there is nothing in terms of significance, but an open bracket, closed bracket if there is at least one significance indicator and commas if there is more than one letter. So if it is the last significance level, there would be a closed bracket, if there is at least one more coming, there would be a comma and a space) 3) How do I get the superscript formatting for everything from the fourth character on? Thanks a bunch, I will give Santa Claus a letter of recommendation for all those who make suggestions. Katsche Reply Barb Reinhardt Dec 28, 1:10 pm show options Newsgroups: microsoft.public.excel.misc From: "Barb Reinhardt" - Find messages by this author Date: Wed, 28 Dec 2005 13:10:05 -0500 Local: Wed, Dec 28 2005 1:10 pm Subject: Merging Info in Two Cells Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse I can help with part of it 1) When I merge the cells, I get endless decimals, i.e. .4653988798798(B) for the first column. Assuming 0.465 ... is in A2 and (B) is in B2 Try =TEXT(A2,"0%")&B2 wrote in message |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Merging Info in Two Cells
This will format the entire range at one time:
Sub FormatRange() With Range("c5:g5").Characters(Start:=4, Length:=9).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Superscript = True .ColorIndex = 23 End With End Sub Mike F wrote in message oups.com... I did get a partial response, thanks Barb, but now I need to dig into VBA and I stumble. I guess the formatting needs to be done in VBA and the easiest way is by copying each cell and pasting the values (otherwise, I am not sure if I can do partial formatting of a cell, i.e. superscripting parts of it). So the code would read: Range("c5:g5").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False With ActiveCell.Characters(Start:=4, Length:=9).Font .Name = "Arial" .FontStyle = "Bold" .Size = 12 .Strikethrough = False .Superscript = True .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 23 End With The Problem is the Range. For Copying:Pasting, I can do such a range but not for the formatting, i.e. I would need an individual range for each cell. How do I do that in a loop command, i.e. something like dim intCol as integer for intCol = 1 to 10 if intCol <= 10 then ----- all that formatting ---- end if next intCol This does not work for whatever reason, is anybody able to help out? Thanks Katsche Hi Group, I have a question on using VBA for merging information in cells and changing the format. We do have a printout from a tabulation program (WinCross) which looks the following: Blue Green Yellow (A) (B) (C) 46.5% 35.4% 43.0% Bc AC aB That means, Blue (46.5%) is significantly different at the 95% level from Green and at the 90% level from Yellow This is spit out for about 100 tables. We now want to merge some of that info and also have the format changed, i.e. Blue Green Yellow 47%(B) 35%(A,C) 43%(B) In words: we need the percentage without the decimals and then the capital letters in parentheses, separated with a comma. To make things worse, it should also have the (A,C) et al. in superscript. Is there a way how I can do that in VBA? I have a rough idea how to get the two cells together, but I stumble over three things: 1) When I merge the cells, I get endless decimals, i.e. .4653988798798(B) for the first column. 2) I would need nothing if there is nothing in terms of significance, but an open bracket, closed bracket if there is at least one significance indicator and commas if there is more than one letter. So if it is the last significance level, there would be a closed bracket, if there is at least one more coming, there would be a comma and a space) 3) How do I get the superscript formatting for everything from the fourth character on? Thanks a bunch, I will give Santa Claus a letter of recommendation for all those who make suggestions. Katsche Reply Barb Reinhardt Dec 28, 1:10 pm show options Newsgroups: microsoft.public.excel.misc From: "Barb Reinhardt" - Find messages by this author Date: Wed, 28 Dec 2005 13:10:05 -0500 Local: Wed, Dec 28 2005 1:10 pm Subject: Merging Info in Two Cells Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse I can help with part of it 1) When I merge the cells, I get endless decimals, i.e. .4653988798798(B) for the first column. Assuming 0.465 ... is in A2 and (B) is in B2 Try =TEXT(A2,"0%")&B2 wrote in message |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protection of worksheet / merging Cells | Excel Worksheet Functions | |||
Merging cells in Excel 2003 | Excel Discussion (Misc queries) | |||
change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) | |||
Excel should have the option to merge contents when merging cells. | Excel Discussion (Misc queries) | |||
Merging Cells but have each cell counted in the range of merged c. | Excel Worksheet Functions |