Alignment problems

[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.

 

[Paul Stevens]

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

 

[Tom Ogilvy]

for each cell in Selection
if application.IsText(cell) then
cell.HorizontalAlignment = xlHAlignLeft
else
cell.HorizontalAlignment = xlHAlignCenter
end if
Next

 

[Tom Ogilvy]

Just a thought:
Custom number formats
a) don't do much for text
b) don't affect alignment

 

[Paul Stevens]

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

 

[Tom Ogilvy]

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.

 

[Paul Stevens]

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

 

[Tom Ogilvy]

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.

 

[Paul Stevens]

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