what is wrong in this script?

kob

New Member
Joined
Dec 11, 2009
Messages
9
Hello,

When I debug this script I get: division by zero!
In 3 lines over the debugging line, the script are writing out the variable "prosentref" as 1. Whatt can be wrong?
(the value in cell T8769 is 1)

Option Explicit
Sub optimaliseringDrift()
Dim Pgm1 As Integer
Dim Pgm2 As Integer
Dim PL As Integer
Dim lamda_gm1 As Double
Dim lamda_gm2 As Double
Dim deriv1 As Double
Dim deriv2 As Double
Const Pgm1_max As Integer = 200
Const Pgm2_max As Integer = 300
Const Pgm1_min As Integer = 45
Const Pgm2_min As Integer = 40
Const deltaP As Integer = 5
Dim i As Integer
Dim ef As Double
Dim nbv As Double
Dim nel1 As Double
Dim nel2 As Double
Dim dellast1 As Double
Dim dellast2 As Double
Dim prosentref As Double
Dim slingring1 As Double
Dim slingring2 As Double
Dim doteller As Integer

PL = ActiveSheet.Range("D2").Value
Pgm1 = Pgm1_min
dellast1 = Pgm1 / Pgm1_max
prosentref = ActiveSheet.Range("T8769").Value
slingring1 = dellast1 / prosentref
doteller = 0
Do Until slingring1 >= 1
prosentref = ActiveSheet.Range("T8769").Offset(doteller, 0)
slingring1 = dellast1 / prosentref
nel1 = ActiveSheet.Range("U8769").Offset(doteller, 0).Value
doteller = doteller + 1
Loop
ActiveSheet.Range("W8769") = nel1
ActiveSheet.Range("X8769") = prosentref
ActiveSheet.Range("Y8769") = doteller
Pgm2 = Pgm2_min
dellast2 = Pgm2 / Pgm2_max
prosentref = ActiveSheet.Range("T8769").Value
slingring2 = dellast2 / prosentref
doteller = 0
ActiveSheet.Range("Y8770") = doteller
ActiveSheet.Range("X8770") = prosentref
Do Until slingring2 >= 1
prosentref = ActiveSheet.Range("T8769").Offset(doteller, 0)
slingring2 = dellast2 / prosentref (debugging line)
nel2 = ActiveSheet.Range("U8769").Offset(doteller, 0).Value
doteller = doteller + 1
Loop
ActiveSheet.Range("W8770") = nel2
ActiveSheet.Range("X8770") = prosentref
ActiveSheet.Range("Y8770") = doteller

'ef = ActiveSheet.Range("R10").Value
'nbv = ActiveSheet.Range("P10").Value

'deriv1 = ef / (nbv * nel1)
'deriv2 = ef / (nbv * nel2)
End Sub
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
sorry!!!

The value of the doteller is 81 at the moment of error!!
Why??
I defined the variable doteller = 0 5 lines over

Because the code has looped 81 times adding 1 to doteller each time

So now, you need to see the value in
ActiveSheet.Range("T8769").Offset(81,0)
which is
ActiveSheet.Range("T8850")
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
OK, 1 see 2 choices in the future for you...

1. Step through the code using F8, and watching the values of all the variables.
Then figuring out what is happening, do the variables change/incriment as you have intended them to?
check the values of the cells after the macro puts values into them.
Are the cells used by the macro populated by formulas? Are those formulas calculated correctly?

2. Start over, Instead of posting code that doesn't work, and hoping we can decipher it. Try describing in words what you want the code to do..
That code seems way overcomplicated. And is VERY difficult for us to read and figure out what it's meant to do.
Perhaps there is a much simpler way..
 

kob

New Member
Joined
Dec 11, 2009
Messages
9
But in Line number 49 (5 lines over the error) I defined doteller as Zero?
Why do not that count?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

It does count...

But your second loop, started a new loop, and ran sucessfully 81 times, adding 1 to doteller each time.
 

kob

New Member
Joined
Dec 11, 2009
Messages
9
ok, I see.

Then my little loop there ran out! I must have a closer look at the conditions inside the loop.

Thanks a lot.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
Members
415,920
Latest member
ExcelNoob28

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top