Excel Multiplication 1

[randy700]

I have several macros that upload data from Excel to the mainframe via the IBM MQ series. This one is presenting a problem because it stops when it reaches a certain cell.

Here is the code that is giving me trouble:

strTempData = ActiveCell.Value * 1000
strTempData = Space(WidthArray(nbrCols) - Len(strTempData)) & strTempData

The value of the active cell is 34.685.
The value of WidthArray(nbrCols) is 5.
The purpose of this code is to upload the data with leading zeroes and no decimal point. The next step replaces the spaces with zeroes.

In hundreds of cells, this works.
Example 1:
Active cell = 4.202
Multiply by 1000 = 4202
Replace leading space with zero = 04202

Example 2:
Active cell = 24.113
Multiply by 1000 = 24113
No leading space to replace with zero

In this cell, it should be:
Active cell = 34.685
Multiply by 1000 = 34685
No leading spaces to replace.

Stepping through the code reveals the problem.
Active cell = 34.685
Multiply by 1000 = 34684.9999999999

Can someone tell me how to correct this?


Randy

 

[PHV]

I'd try this:

strTempData = Int(ActiveCell.Value * 1000)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

That doesn't correct the problem.
With a value of 34.685, using your suggestion, results in 34684 - I need 34685.


Randy

 

[PHV]

And this ?

strTempData = Round(ActiveCell.Value * 1000, 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Excellent!
That appears to do the trick.

Thank you, I've been fighting with this for 2 days.


Randy