VBA Userform not calculating correctly

[TMal830]

Hello,

I'm trying to figure out how much freight I am paying per sq ft of a product I sell. I'm entering all information into a userform. I enter the "Freight" which is the total freight I'm paying, the "sqftsum" is the total amount of square feet, and the "frsqft" is the freight per square foot that needs to be calculated. When I use the code below, assuming the total freight was $5 for a total of 173.45 square feet, the total it gives me is 2.88267512251. It should be 0.0288267512251. No idea why it's moving the decimal place over 2 spots, and if I try dividing the answer by 100 nothing happens. Hoping someone can help me make sense of this! Any help would be appreciated. Thanks.

---

If Not Freight = "" Then
x = 1
sqftsum = 0
Do
sqftsum = sqftsum + ListBox1.List(x, 3)
x = x + 1
frsqft = (Freight / sqftsum)
Loop Until x = c
End If

 

[MrIfOnly]

It's hard to see everything you have going on here, but I suspect the issue may be caused by your calculating frsqft after each increment of sqftsum. Try moving this line:

frsqft = (Freight / sqftsum)

to after this line:

Loop Until x = c

Regards,

CJ

 

[TMal830]

It's hard to see everything you have going on here, but I suspect the issue may be caused by your calculating frsqft after each increment of sqftsum. Try moving this line:

frsqft = (Freight / sqftsum)

to after this line:

Loop Until x = c

Regards,

CJ

That didn't work, but I found something pretty interesting. Even if I remove the variables and replace them with the actual value, I step through (f8) and the value still comes up with the decimal point moved over 2 spaces. What's even weirder is that I can change the "5" to "500" and still come up with the exact same value. Does the same happen to you if you just use this one line in your code?

c = (5 / 173.45)
c = (500 / 173.45)

 

[MARK858]

Probably more a display issue as it comes out for me in debug.print as 2.88267512251369E-02 which is as correct as Excel gets.

Sub test1()
Dim frsqft As Double, sqftsum As Double, Freight As Long
Freight = 5
sqftsum = 173.45
frsqft = (Freight / sqftsum)
Debug.Print frsqft
End Sub

 

[TMal830]

That's the problem though, it moved over the decimal point 2 spaces. 5 divided by 173.45 should be 0.0288267512251. If you change that "5" to "500" you get the exact same answer for some reason. Anyone have any ideas?

 

[MARK858]

E-02 is scientific notation for the number being 2 decimal places across.

I see there is a problem with the 500 but the below gives the correct answer with E-04 showing.

Sub test1()
Dim frsqft As Double, sqftsum As Double, Freight As Long
Freight = 5
sqftsum = 173.45
frsqft = (Freight / sqftsum) / 100
Debug.Print frsqft
End Sub

 

[TMal830]

Thanks so much for the info. The scientific notation is new to me so it wasn't clicking for me. Thanks again!

 

[MrIfOnly]

You could get around the scientific notation by using Round:

frsqft = Round(Freight / sqftsum,5)

CJ

 

[MARK858]

You could get around the scientific notation by using Round:

frsqft = Round(Freight / sqftsum,5)

CJ

It doesn't fix the issue with using 500 as the variable unfortunately...

Sub test1()
Dim frsqft As Double, sqftsum As Double, Freight As Long
Freight = 500
sqftsum = 173.45
frsqft = Round(Freight / sqftsum, 5)
Debug.Print frsqft
End Sub

Result = 2.88268