Merging Info in Two Cells

[Katsche.Schwarzenbeck]

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 Copyingasting, 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" <[email protected]> - Find messages by this
author
Date: Wed, 28 Dec 2005 13:10:05 -0500
Local: Wed, Dec 28 2005 1:10 pm
Subject: Re: 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

<[email protected]> wrote in message

 

[Mike Fogleman]

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