Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Overflow error during division within a variable 2

Status
Not open for further replies.
ljsmith91

ljsmith91

Programmer
May 28, 2003
305
US
I am trying to do simple division into a variable and I am getting an error overflow on my DrPct_2200= statement below.

I know it may have something to do with how my variables are defined but I have tried Integer, Numeric, String, Long and Variant with no success. Here is the code:
Code: 
Dim Drive_Count As Variant
Dim DrPct_2200 As Variant
Dim Drive_2200 As Variant

Drive_Count = Drive_Cell.Offset(0, 2)
Drive_2200 = Drive_Cell.Offset(0, 34)   
DrPct_2200 = Drive_2200 / Drive_Count
Drive_Count as an example is 10 and Drive_2200 is 7 then I want DrPct_2200 to end up equalling "70%". I will have to figure out how to add the percent sign after I get my overflow problem fixed.

Can you help?

Thanks.

 
Sort by date Sort by votes
hi,
Code: 
Dim Drive_Count As Variant
Dim DrPct_2200 As Variant
Dim Drive_2200 As Variant

Drive_Count = Drive_Cell.Offset(0, 2)
Drive_2200 = Drive_Cell.Offset(0, 34) 
if Drive_Count <> 0 then
  DrPct_2200 = Drive_2200 / Drive_Count 
else
  'what do you want to do when Drive_Count is ZERO???
end if


Skip,

[glasses] [red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [bomb][tongue]
 
Upvote 0 Downvote
Skip,

Drive_Count is never zero....I failed to show the earlir part of the code. If it is zero, it never reaches this point in the code. So I know I have a numeric value in Drive_Count and I usually have a numeric in Drive_2200 although that could be "0" which should become 0% in my DrPct_2200 variable if I ever get it working. Any ideas ?

Thanks.
 
Upvote 0 Downvote


What is the value in Drive_Count at the time of the overflow?

Skip,

[glasses] [red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [bomb][tongue]
 
Upvote 0 Downvote
Overflow occurs on first pass through when drive_Count = "5" and Drive_2200 = "5" also. I never get to the 100 other drives in the loop process.
 
Upvote 0 Downvote
I have another optional way I would like to do it....right within an Excel cell. Is it possible to set up the cell with the Drive_2200 value and the a formula that gets me the percentage used. Something like this....of course this is not working:

ActiveCell.Formula = Drive_2200 & "=SUM(Drive_2200 / Drive_Count)" & "%"

Can a cell contain a formula and a value at the same time and if so, how ?

I think I like this option better if I can get it to work.

 
Upvote 0 Downvote

Don't understand what you're trying to do. the SUM function does not make sense.

The answer to your previous post...

How do you KNOW that each variable contains "5"? Can you see these values in the Watch Window?

How to use the Watch Window as a Power Programming Tool faq707-4594


Skip,

[glasses] [red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [bomb][tongue]
 
Upvote 0 Downvote
Skip,

I knew the values through msgbox's prior to the commands. Anyway, in regards to my SUM above, I badly asked if you could have a formula along with a non- formula value in the same cell. The results looks like this in a cell [ 7 70%] where the 70 was set by a formula and the 7 and "%" were fixed values. The cell would tell me the number of drives in use along with the pct used. That was wishful thinking on my part as it seems it can only accept a formula.

Instead of doing the division through vba code, I used the SUM feature within an Excel cell to et my division to work so I am all set but I still do not understand why I could not get it to work through VBA code.

Thanks for your assistance. Sorry I have been so cryptic.
 
Upvote 0 Downvote
Use of the SUM function is unnecessary unless you are adding a range of cells as in:

=SUM(A1:B5)

all you are doing is division, so your cell formula just needs to be:

=A1/A2

(cell references as appropriate)

Hope this helps.
 
Upvote 0 Downvote


As far as the percent sign, why can't you FORMAT the cell as percent? No need to do handstands to concatnate a % character. In fact, unless the value is ONLY and ALWAYS to be used as a display value and will NEVER be used for arithmetic computations.

But if you have a numeric value that needs to be formatted that is concatenated in a string...
Code: 
n = 7 / 10
MyVal = "7 " & Format(n, "0.0%")

On your original question the only way that an overflow could occur is if your divisor is very nearly ZERO. then the quotient would be ENORMOUS.

So I repeat, use the Watch Window to view the ACTUAL value.

Skip,

[glasses] [red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [bomb][tongue]
 
Upvote 0 Downvote
Thanks. Both suggestions are a big help. I am getting this though it is a slow process. I have to make a new post on another head scrather for me. Thanks.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
998
morechocolate
morechocolate
huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
368
strongm
strongm
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
463
sal21
sal21

Part and Inventory Search

Sponsor

Back
Top