Alignment problems

  • Thread starter Thread starter Shibashis
  • Start date Start date
S

Shibashis

In a cell the formula says =if(a1>0,a2,"nil"). But the
problem is aligning right for numbers and Center for
text. In early versions you could precede with Single
quote or ^ or dbl quote for appropriate alignment.
I'm just a beginner in programming. Another problem is
thelarge number of data so I have to move progressively
from one cell to another with the query. My knowledge of
VBA gets me stuck after:

With Selection
For each Cell

Basically I can only stepover a Macro and make changes.
Is there any other way out without VBA?
Please help.
 
Have you tried custom formats.
You can format for individually for +ve ; -ve ; zero
(separated with semicolons).

You could change your formula to if(a1>0,a2,0). Custom
format for +ve numbers (eg General or whatever), then
after the second semicolon type "nil " with some spaces.
Help > Custom formats might give more options for control
of alignment.

Regards,
Paul
 
for each cell in Selection
if application.IsText(cell) then
cell.HorizontalAlignment = xlHAlignLeft
else
cell.HorizontalAlignment = xlHAlignCenter
end if
Next
 
Just a thought:
Custom number formats
a) don't do much for text
b) don't affect alignment
 
I could have misunderstood the OP's objective, which I was
guessing was simply to display "nil" center aligned in his
formula cell if A1 is <=0 or otherwise A2.

I appreciate custom formats do not change text formatting,
possibly I should have added " of 'nil' which should not
be regarded as text if in a custom format" to the last
sentence of my post. However I think what I proposed
would achieve something close to his objective, if indeed
I correctly understood it.

If so, with a little playing around the OP could display
his "nil", more or less center aligned, with no code and
always updated on calculation.

Regards,
Paul
 
Didn't intend to cast a negative light on your suggest. I said, "Just a
thought" to indicate this was additional information. You seem to
understand the request just fine. Sorry.
 
Tom, with all the help I've gleaned from your posts in
this ng (and many thanks) I would only take any comment of
yours as constructive. In this case a little reminder to
be try and be a little more explicit.

As you say, hopefully your "Just a thought" will have
clarified any ambiguity in my earlier post.

Regards,
Paul
 
In fairness, your suggestion was both original and clever (although with
changeable columns widths and proportional fonts, might be troublesome). I
didn't give it as thorough a read as I might have - so my additional
information, while substantially correct was tangential (you did replace the
text with 0). Your post was clear in what you intended. So, clearly, my
Bad and my apologies.
 
Thanks for that, boosted my confidence!

When reading other peoples threads I often find exchanges
of this nature useful. Either to get a fuller picture or
better understand what I didn't initially pick up.

So for the body of this ng I would say your comments were
indeed worthwhile, even if this has been a relatively
trivial XL topic.

Regards.
Paul
 
Back
Top