VBA Code Im Stuck on dividing a variant by 100 to get percentage

[szita2000]

Hi Guys.

It is Friday, and yay for that but for the love of god I can't work this one out.

I am trying to crunch some reporting data from a htm file.

The bit I'm stuck is that I have a Variant declared, which starts it's life out as a string (due to a comma as a decimal in the data)
But then I use worksheetfunction.substitute to replace this with a decimal and that converts it into a Variant/Long Datatype.

When I do some calculus with these values (one divided by the other) I expect to see a 0.000 result.

However excel returning This in the immediate window for me:

On the 28 day, Machine A01 - PRCalc = 0.98366048
On the 28 day, Machine A01 - PDTCalc = 5.41666666666667E-03
On the 28 day, Machine A01 - UPDTCalc = 1.09228533333334E-02

The bold bits should read 0.54% and 1.09%

Here is the piece of code doing the simple calculation

'PRCalc calculation
    If T > 1 Then
        PRCalc = (U2 * A) * 1
    Else
        PRCalc = PR
    End If

'PDT calculation
        PDTCalc = PDT / SPP
    
'UPDT calculation
    If T > 1 Then
        UPDTCalc = 1 - ((PDT / SPP) + PRCalc)
    Else
        UPDTCalc = 1 - PRCalc - PDTCalc
    End If

Then I tried this:

'PRCalc calculation
    If T > 1 Then
        PRCalc = (U2 * A) * 1
    Else
        PRCalc = PR
    End If


'PDT calculation
        PDTCalc = (PDT / SPP) [B][COLOR=#ff0000]/ 100[/COLOR][/B]
'UPDT calculation
    If T > 1 Then
        UPDTCalc = (1 - ((PDT / SPP) + PRCalc)) [B][COLOR=#ff0000]/ 100[/COLOR][/B]
    Else
        UPDTCalc = (1 - PRCalc - PDTCalc) [COLOR=#ff0000][B]/ 100[/B][/COLOR]
    End If

But the results are the same.
How can I divide these numbers to show percentages?

Any help much appreciated.

Thanks

Thomas

Let me know if you need it, I can post the whole code (which is admittedly a bit clunky... )

 

[steve the fish]

5.41666666666667E-03 is equal to 0.54%. The same number with different formatting.

 

[Tim_Excel_]

How about using the .NumberFormat = "0.00" function to transform your data to the right format?

 

[szita2000]

Hey guys.

Thanks very much for the help.

I wrote out the values to a cell.value in the report and it is correct!

Before I tagged the text on to the value itself "PR% value " & PDTCalc so it formatted as text hence given me the scientific formatting.
Now I removed the text and the value displays correctly.

Thanks!

 

[steve the fish]

ADVERTISEMENT

You could format within VBA like:

myCell = "PR% value " & Format(PDT / SPP, "0.00%")

 

[szita2000]

Hi Steve.

I never thought of that!
I tried

With PDTCalc[INDENT].Numberformat = "0.00%"[/INDENT]
[INDENT].value = .value[/INDENT]
End With

But I got an error saying With requires an object.

Then I tried to Set the variable at the calculation, but that thrown an error too.

I will try this now.

Thanks

 

[steve the fish]

ADVERTISEMENT

You use numberformat to format a cell then place the value in the cell. eg

With Sheets("Sheet1").Range("A1")[INDENT].Numberformat = "0.00%"
.value = PDTCalc
[/INDENT]
End With

 

[szita2000]

Aaaah.

And the Cell would have been my object then.
No?

Makes sense now.

 

[steve the fish]

Yes a cell range is an object.