software

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(quot;c5:g5quot;).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With ActiveCell.Characters(Start:=4, Length:=9).Font
.Name = quot;Arialquot;
.FontStyle = quot;Boldquot;
.Size = 12
.Strikethrough = False
.Superscript = True
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 23
End WithThe 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 lt;= 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: quot;Barb Reinhardtquot; gt; - 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

gt; 1) When I merge the cells, I get endless decimals, i.e.
gt; .4653988798798(B) for the first column.

Assuming 0.465 ... is in A2 and (B) is in B2
Try
=TEXT(A2,quot;0%quot;)amp;B2

gt; wrote in messageThis will format the entire range at one time:
Sub FormatRange()

With Range(quot;c5:g5quot;).Characters(Start:=4, Length:=9).Font
.Name = quot;Arialquot;
.FontStyle = quot;Boldquot;
.Size = 12
.Superscript = True
.ColorIndex = 23
End With

End Sub

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