This seems like it should be easy, but i'm baffled!! In the code below,I want
to put the quotient of Cells E20 and E38, both of which contain positive
integers, into Cell G40. The only value I get is zero. The code runs ok but
obviously produces the wrong result. Can anyone tell me why this is? What am
I missing? Thanks.

Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = Application.WorksheetFunction.Quotient(x, y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print

End Sub

JCIrish,
This is what I came up with as a fix:
Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = (x / y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print
End Sub
I got it to run and give the right answer. I just substituted
"AssetAllocation" with "Sheet1". As to your first question, why - I think it
may be your syntax.
I put in what the "Ratio" was instead of letting VB find a function.

hth

"JCIrish" wrote:

This seems like it should be easy, but i'm baffled!! In the code below,I want
to put the quotient of Cells E20 and E38, both of which contain positive
integers, into Cell G40. The only value I get is zero. The code runs ok but
obviously produces the wrong result. Can anyone tell me why this is? What am
I missing? Thanks.

Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = Application.WorksheetFunction.Quotient(x, y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print

End Sub

"JCIrish" wrote:
This seems like it should be easy, but i'm baffled!!
[....]
The only value I get is zero.

So am I, but not for the reason you might think.

What version of Excel/VBA are you using?


Ratio = Application.WorksheetFunction.Quotient(x, y)

WorksheetFunction.Quotient is not valid in my revision of Excel 2003 / VBA
6.3. So I get a VBA error, not zero.

The simple fix, indeed the better implementation unless you are trying to
test the VBA implementation of WorksheetFunction.Quotient, assuming that
works in your version of Excel/VBA:

Ratio = Int(x / y)


I want to put the quotient of Cells E20 and E38, both of which
contain positive integers, into Cell G40. The only value I get is zero.

What values have you tried in E20 and E38?

Zero is the correct answer when E20 < E38.

When E20 is 12 and E38 is 5, G40 is 2 with "Ratio=Int" fix above.


----- original message -----

"JCIrish" wrote in message
...
This seems like it should be easy, but i'm baffled!! In the code below,I
want
to put the quotient of Cells E20 and E38, both of which contain positive
integers, into Cell G40. The only value I get is zero. The code runs ok
but
obviously produces the wrong result. Can anyone tell me why this is? What
am
I missing? Thanks.

Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = Application.WorksheetFunction.Quotient(x, y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print

End Sub

Thanks so much, FloMM2. The change you suggested did the trick --- it solved
my problem. Your help is much appreciated

jcirish

"FloMM2" wrote:

JCIrish,
This is what I came up with as a fix:
Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = (x / y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print
End Sub
I got it to run and give the right answer. I just substituted
"AssetAllocation" with "Sheet1". As to your first question, why - I think it
may be your syntax.
I put in what the "Ratio" was instead of letting VB find a function.

hth

"JCIrish" wrote:

This seems like it should be easy, but i'm baffled!! In the code below,I want
to put the quotient of Cells E20 and E38, both of which contain positive
integers, into Cell G40. The only value I get is zero. The code runs ok but
obviously produces the wrong result. Can anyone tell me why this is? What am
I missing? Thanks.

Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = Application.WorksheetFunction.Quotient(x, y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print

End Sub

Thanks for the help, Joe User. I'm using Excel 2007 (I don't know how to
determine the VBA version. How can I find that out?). Your solution works
better for me if I omit the INT and just use the ratio (x/y) as suggested by
FloMM2. Thanks.

jcirish

"Joe User" wrote:

"JCIrish" wrote:
This seems like it should be easy, but i'm baffled!!
[....]
The only value I get is zero.

So am I, but not for the reason you might think.

What version of Excel/VBA are you using?


Ratio = Application.WorksheetFunction.Quotient(x, y)

WorksheetFunction.Quotient is not valid in my revision of Excel 2003 / VBA
6.3. So I get a VBA error, not zero.

The simple fix, indeed the better implementation unless you are trying to
test the VBA implementation of WorksheetFunction.Quotient, assuming that
works in your version of Excel/VBA:

Ratio = Int(x / y)


I want to put the quotient of Cells E20 and E38, both of which
contain positive integers, into Cell G40. The only value I get is zero.

What values have you tried in E20 and E38?

Zero is the correct answer when E20 < E38.

When E20 is 12 and E38 is 5, G40 is 2 with "Ratio=Int" fix above.


----- original message -----

"JCIrish" wrote in message
...
This seems like it should be easy, but i'm baffled!! In the code below,I
want
to put the quotient of Cells E20 and E38, both of which contain positive
integers, into Cell G40. The only value I get is zero. The code runs ok
but
obviously produces the wrong result. Can anyone tell me why this is? What
am
I missing? Thanks.

Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = Application.WorksheetFunction.Quotient(x, y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print

End Sub

.

"JCIrish" wrote:
Thanks for the help, Joe User. I'm using Excel 2007 (I don't know
how to determine the VBA version. How can I find that out?).

I cannot say for sure, since I know that Excel 2007 changed navigation in
many cases. In Excel 2003 VBA, I click on Help About Microsoft Visual
Basic.


Your solution works
better for me if I omit the INT and just use the ratio (x/y)

Then you did not want Quotient in the first place. Alternatively, you are
misleading yourself by formatting G40 to have zero decimal places or because
you happen to choose integers in E20 and E38 such that it does not matter
(e.g. 16 and 8).

I suggest that you try your macro with E20 set to 16, E38 set to 10, and be
sure that the result in G40 is displayed as you wish, be it 1 (Quotient),
1.6, or 2 (1.6 rounded).

Also note that in the latter two cases, the true value in the cell is 1.6,
even if it displays as 2. So =2*G40 will display as 3.2 or 3 (formatted
with zero decimal places) instead of 4.

The point is: QUOTIENT returns the truncated integer part of the division
of two numbers, whereas x/y returns the exact result of the division. Int
also returns the truncated integer of its argument.

QUOTIENT(16,10) and Int(16/10) result in 1, whereas 16/10 is about 1.6.

Heads-up: Someone might suggest the following as an alternative solution:

Dim Ratio as Long

Although that does convert x/y to an integer, it is the __rounded__ result
of the division, not the truncated integer that QUOTIENT returns. So,
again, Ratio=16/10 results in the integer 2 instead of the integer 1.

The correctness of any solution depends on what you want as a result. I
ass-u-me-d you wanted the truncated integer since you wrote
WorksheetFunction.Quotient in the first place.


----- original message -----

"JCIrish" wrote in message
...
Thanks for the help, Joe User. I'm using Excel 2007 (I don't know how to
determine the VBA version. How can I find that out?). Your solution works
better for me if I omit the INT and just use the ratio (x/y) as suggested
by
FloMM2. Thanks.

jcirish

"Joe User" wrote:

"JCIrish" wrote:
This seems like it should be easy, but i'm baffled!!
[....]
The only value I get is zero.

So am I, but not for the reason you might think.

What version of Excel/VBA are you using?


Ratio = Application.WorksheetFunction.Quotient(x, y)

WorksheetFunction.Quotient is not valid in my revision of Excel 2003 /
VBA
6.3. So I get a VBA error, not zero.

The simple fix, indeed the better implementation unless you are trying to
test the VBA implementation of WorksheetFunction.Quotient, assuming that
works in your version of Excel/VBA:

Ratio = Int(x / y)


I want to put the quotient of Cells E20 and E38, both of which
contain positive integers, into Cell G40. The only value I get is zero.

What values have you tried in E20 and E38?

Zero is the correct answer when E20 < E38.

When E20 is 12 and E38 is 5, G40 is 2 with "Ratio=Int" fix above.


----- original message -----

"JCIrish" wrote in message
...
This seems like it should be easy, but i'm baffled!! In the code
below,I
want
to put the quotient of Cells E20 and E38, both of which contain
positive
integers, into Cell G40. The only value I get is zero. The code runs ok
but
obviously produces the wrong result. Can anyone tell me why this is?
What
am
I missing? Thanks.

Sub ComputeAssetRatios()

Application.Worksheets("AssetAllocation").Activate

Dim Numerator As Range
Dim Denominator As Range
Dim Ratio
Dim x
Dim y
Dim z

Set Numerator = Worksheets("AssetAllocation").Range("E20")
x = Numerator.Value
Debug.Print

Set Denominator = Worksheets("AssetAllocation").Range("E38")
y = Denominator.Value
Debug.Print

Ratio = Application.WorksheetFunction.Quotient(x, y)
Range("G40").Select
Selection.Value = Ratio

Debug.Print

End Sub

.