a) char(10) and b) asterisks for borders?

[Lostguy]

Hello.

a) For three HRT's, you can use char(10)&char(10)&char(10).
Is there a way to shortcut this to char(10)*3, or similar?

b) Also, in Excel 2003, is there a way to put a border of asterisks
around a cell without using VBA? Is there a way to make a thick blue
border around a cell?

(On the Format Cells>Border, I can't see a way to increase the
thickness of the borders beyond the few options the presets give
you..)

??

All help appreciated!

VR/

Lost

 

[Rick Rothstein \(MVP - VB\)]

You should be able to use this...

String(3,10)

where the first argument is how many you want and the second argument is the
ASCII and/or ANSI value; or the second argument could be the character
itself, that is, this would work too...

String(3, Chr(10))

Rick

 

[Rick Rothstein \(MVP - VB\)]

By the way, VB has a pre-defined constant for Chr(10) which is vbLf (where
Lf stands for Line Feed, which is what you are calling HRT). So, you could
do these also...

vbLf & vbLf & vbLf

or

String(3, vbLf)

as well as what I posted earlier.

Rick

 

[Gord Dibben]

For the b) question.

Asterisks?.........none that I know of.

For a thicker bule line, use the drawing toolbar to draw a rectangle over the
cell.

Format to transparent then set a blue line weight of your choice.

4 or 5 pts gives a thick line.


Gord Dibben MS Excel MVP

 

[Lostguy]

All,

Thanks!

Rick: I tried ="a"&String(2, vbLf)&"b", ="a"&String(2, CHAR(10))&"b",
and ="a"&String(2, 10)&"b" and all produce the #name? error.

??

Gord: Perfect! Your idea chopped 1-3K off of my file sizes! (which is
what I was trying to do). I had been using rows and columns of cells
that were blue-colored to make a frame. With your method, I was able
to delete those. Thanks!

VR/

John

 

[Rick Rothstein \(MVP - VB\)]

=> Rick: I tried ="a"&String(2, vbLf)&"b", ="a"&String(2, CHAR(10))&"b",

and ="a"&String(2, 10)&"b" and all produce the #name? error.

I have to apologize to you... because you posted in the programming
newsgroup, I assumed you were looking for a VB solution (developing
worksheet formulas are not really considered "programming"); however, I
should have realized that you wanted a formula response by your use of the
CHAR function... problem is, that spelling is very close to VB equivalent
function Chr... again, because you posted in a programming group, my mind
simply saw Chr and not CHAR. Anyway, there is a similar solution at the
worksheet formula level in the REPT function. For example, try this...

="A"&REPT(CHAR(10),3)&"B"

but make sure you format your cell(s) for word wrap or you won't see the
result.

Rick