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

#### szita2000

##### Board Regular
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

Code:
``````'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:
Code:
``````'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... )

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
5.41666666666667E-03 is equal to 0.54%. The same number with different formatting.

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

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!

You could format within VBA like:

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

Hi Steve.

I never thought of that!
I tried
Code:
``````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

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

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

Aaaah.

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

Makes sense now.

Yes a cell range is an object.

Replies
0
Views
449
Replies
1
Views
2K
Replies
3
Views
810
Replies
3
Views
2K
Replies
11
Views
2K

1,196,412
Messages
6,015,117
Members
441,871
Latest member
lajervik

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back