Hi Rick,

In a flash of programming inspiration, I noticed that some of my variables
were declared as Single Precision. So quickly, I did a Ctrl+H and replaced
all "Dim ... As Single" to "Dim ... As Double" thruout all my macros. I then
commented out the On Error Resume Next and On Error GoTo 0 as shown below. I
then ran the macro containing this code umpteen times and the overflow error
no longer occurs. Ray!!!

Dim NumShares As Double
Dim NumSharesLong As Long
' On Error Resume Next
NumShares = Round(aNumShrs(lRow, 1), 2)
NumSharesLong = NumShares
' On Error GoTo 0
If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1)
TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1)
TotalSharesPurchased = TotalSharesPurchased + NumSharesLong

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick


"Rick Rothstein" wrote:

Obviously, this is not your full code... your use of lRow seems to indicate
that the code you showed us is in a loop. And, of course, we cannot see your
other values in order to see what is going on during the calculations. I
will tell you, though, I would not trust simply using On Error Resume Next
to solve your problem... doing that in this situation would be just begging
for trouble.

Can you tell me how many rows of data have been processed when the error
occurs... do you get past the first row at all? Do you always declare all of
your variables? If yes, do you use Option Explicit at the top of your
modules? If not, put that statement at the top of your module and run your
code again... if you do declare all your variables and you don't use Option
Explicit at the beginning of the module, then I am expecting you to see a
misspelled variable name message popup when you run your code.

If this above is not the case, can you send me your workbook so I can see
the error happen for myself and, hopefully, be able to trace it to its
source? Make sure you take out the NO.SPAM stuff from my address before
sending it.

--
Rick (MVP - Excel)


"MichaelDavid" wrote in message
...
Greetings! The below code generates an overflow error when aNumShrs(21, 1)
contains 15749.9999602351, and the macro executes the line which reads:
TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1)

Dim aNumShrs As Variant
Dim aPrice As Variant
Dim TotalPurchases As Double
Dim TotalSharesPurchased As Long

If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1)
TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1)
TotalSharesPurchased = TotalSharesPurchased + aNumShrs(lRow, 1)

When I modified the above code as follows:

Dim aNumShrs As Variant
Dim aPrice As Variant
Dim TotalPurchases As Double
Dim TotalSharesPurchased As Long
Dim NumShares As Double
Dim NumSharesLong As Long

NumShares = Round(aNumShrs(lRow, 1), 2)
NumSharesLong = NumShares
If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1)
TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1)
TotalSharesPurchased = TotalSharesPurchased + NumSharesLong

I still get the overflow error, but I can tell by holding my cursor over
the
variables in the above, that the right answer is being generated. But the
overflow error prohibits the program from continuing. So I then added On
Error Resume Next to the above logic as follows:

On Error Resume Next
NumShares = Round(aNumShrs(lRow, 1), 2)
NumSharesLong = NumShares
On Error GoTo 0
If aPrice(lRow, 1) - MaxPrice 0# Then MaxPrice = aPrice(lRow, 1)
TotalPurchases = TotalPurchases + aPrice(lRow, 1) * aNumShrs(lRow, 1)
TotalSharesPurchased = TotalSharesPurchased + NumSharesLong

Now the logic runs without a hitch and produces the right answer, and the
macro containing the above logic runs to completion. Is there any way to
code
this without using On Error Resume Next?

--
May you have a most blessed day!

Sincerely,

Michael Fitzpatrick

.