Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
Mike Fogleman
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Protection of worksheet / merging Cells dtmd Excel Worksheet Functions 1 November 17th 05 08:52 PM
Merging cells in Excel 2003 Bob Excel Discussion (Misc queries) 1 October 19th 05 07:10 PM
change info in other cells when i change a number in a drop list? macbr549 Excel Discussion (Misc queries) 2 September 11th 05 02:07 AM
Excel should have the option to merge contents when merging cells. Ceiling Tile 01 Excel Discussion (Misc queries) 2 August 31st 05 11:03 PM
Merging Cells but have each cell counted in the range of merged c. gats13 Excel Worksheet Functions 2 November 9th 04 08:14 PM


All times are GMT +1. The time now is 05:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"