If then Else - formatting

[crazy8]

Hi. I am very, very new to this - please help!

I am using this code to help create a fixed-length record I can send to the mainframe from an Excel spreadsheet. In one column, some results are positive, are negative. I am formatting the positive value like this:

MyStr = MyStr & Format(Cells(MyRow, 11).Value, "00000000000000000000")


When the value is negative, I want the format to be different - i.e. I want to align the right-most byte of both the positive and negative values.

Like this:

00000000000000000123

-0000000000000000456


Not like this:

00000000000000000123

-00000000000000000456


Would you please show me how???

Thank you - warm regards,

Paul

 

[Luke M]

First, do note that the variable MyStr will be a true String, as you are using more digits than XL can store for a number (16 significant digits is the limit). You will never have a number that can fill up all the placements.


As to the formatting question, XL uses a format syntax of:

PositiveFormat;NegativeFormat;ZeroFormat;Text

Knowing that, your line of code becomes:

[pre]

MyStr = MyStr & Format(MyRow, "00000000000000000000;-0000000000000000000;00000000000000000000")

[/pre]

 

[Hui]

Paul

I don't think Excel uses more than 15 0's in a custom format.

 

[crazy8]

Thank you - this is so very helpful - it worked! Also,I understand your comment on field-size and have adjusted accordingly.


Follow-up:

I tried using the same logic for another situation with less satisfactory results.

Though this logic seems made for this new situation:

There are nine 2-byte fields.

Each can have a value of either 1, -1, or 0.


I coded this: MyStr = MyStr & Format(Cells(MyRow, 2).Value, "00;-0;00")

The results were: 10, -1, and 00.


I want 01, -1, 00. What is wrong?


Thanks again for helping - I sincerely appreciate it,


Paul

 

[crazy8]

"I want 01, -1, 00. What is wrong?"


OR...' 1', -1, ' 0' would be OK.

 

[Luke M]

I am unable to duplicate your results. When I run the code, I get 01, -1, 00.

Since you're concatenating, does the MyStr already have some value that is messing things up?


Of course, if the cell value really is 10, then the code is also working correctly.

EDIT:

I'm very confused about you're output requirements. The Format method just changes the appearance, it can't change the value. If the cell value is not already 1, -1, or 0, you need to use a different bit of code to determine that. Something like:

[pre]

MyValue = Cells(MyRow, 2).Value
Select Case MyValue
Case > 0
xValue = 1
Case < 0
xValue = -1
Case = 0
xValue = 0
End Select

[/pre]

 

[crazy8]

Yes - it is working.

Apparently your comment was needed for the code to believe it true.


Thanks - I am already breathing much easier.


I can't return the favor with VBA help - but if are interested in learning to play foosball let me know!!!


Best, best regards,

Paul