Overflow error on VBA Module

[Polarfranz]

Hello,
I have two VBA-Modules that are almost the same.
The first looks like this:

Sub Versuch()
    Dim Z As Double
    Dim counter As Integer
    Dim a As Double
    Dim b As Double
    Dim f As Integer
    Dim e As Integer
    Dim WS_Count As Integer
        Dim I As Integer
    
    WS_Count = ActiveWorkbook.Worksheets.Count
    
             
    For I = 1 To WS_Count
        Worksheets(I).Select
        counter = 10
        Z = 1
        a = Cells(8, 6).Value
        b = Cells(9, 6).Value
        f = Cells(3, 6).Value
        e = Cells(4, 6).Value
        
        Do Until Z > e
           Cells(counter, 3).Value = Z
           
           counter = counter + 1
           
           If Z < f Then
            Z = Z + a
           End If
           
           If Z >= f Then
            Z = Z + b
           End If
           
    
    
    
        Loop
    
    Next I
    
End Sub

The second one is slightly changed and gives me an overflow error because of the line: counter = counter +1

Sub twosignificantlayers()
    Dim Z As Double
    Dim counter As Integer
    Dim a As Double
    Dim b As Double
    Dim f As Integer
    Dim e As Integer
    Dim WS_Count As Integer
        Dim I As Integer
    Dim secondlayer As Integer
    Dim secondlayerstep As Integer
    Dim secondtobottom As Integer
    
    
    WS_Count = ActiveWorkbook.Worksheets.Count
    
             
    For I = 1 To WS_Count
        Worksheets(I).Select
        counter = 10
        Z = 1
        a = Cells(8, 6).Value
        b = Cells(9, 6).Value
        f = Cells(3, 6).Value
        e = Cells(4, 6).Value
        secondlayer = Cells(5, 6).Value
        secondlayerstep = Cells(11, 6).Value
        secondtobottom = Cells(15, 6).Value
        
        Do Until Z > e
           Cells(counter, 3).Value = Z
    
           counter = counter + 1
           
           If Z < f Then
            Z = Z + a
           End If
           
           If Z >= f And Z < secondlayer Then
            Z = Z + secondlayerstep
           End If
           
           If Z >= secondlayer Then
           Z = Z + secondtobottom
           End If
    
    
    
        Loop
    
    Next I
    
End Sub

Can anyone tell me why I get this error? It seems like the programme is not jumping to the next worksheet to continue the calculation

Regards,
Franz

[Aflatoon]

It appears you have exceeded the maximum value for an integer (32767). Declare your variables as Long instead.

[snb]

Sub Versuch()    
  For each sh in sheets
      sp=sh.cells(1,6).resize(9)
     sn=sh.cells(10,3).resize(sh.cells(rows.count,3).end(xlup).row-10)  

     for j=1 to ubound(sn)
       z=z+ sp(8-(z<sp(3,1)),1)
       if z>sp(4,1) then exit for
     next
     if j<ubound(sn)+1 then sh.cells(j,3)=z

  next
End Sub

[Polarfranz]

What does ubound(sn) in your code mean? and where do you think I should imply this into my code

[snb]

It should replace your entire code.

[Polarfranz]

Hm that did not really help. I get the error message
"Compile error: Expected array" when using your code.

The point is that I have some raw data and want to recalculate the rawdata in that way:
Let's say I have some rawdata for x and y values that look like this:

1	0.549
2	0.741
3	0.741
4	0.741
5	1.106
6	12.597
7	52.680
8	118.524
9	196.830
10	286.526
11	372.597
12	428.750
13	458.827
14	482.285
15	482.285
16	482.285

Now i want to recalculate the first column. and have not steps in size of 1 from one to the next value, but the steps are only 0.4 for the values from 1 to 5, then 1.2 from 5 to 8 and 0.8 from 8 to 16
These steps shall be shown in column 3. that the final result looks like this:

1	0.549	1
2	0.741	1.4
3	0.741	1.8
4	0.741	2.2
5	1.106	2.6
6	12.597	3.8
7	52.680	5
8	118.524	6.2
9	196.830	7
10	286.526	7.8
11	372.597	8.6
12	428.750	9.4
13	458.827	10.2
14	482.285	11
15	482.285	11.8
16	482.285	12.6

The values for the increasement (here 0.4, 1.2 and 0.8) are given in the excelsheet in the fields F8, F11 and F15 respectively.
The thresholdvalues for the increasementchange (here 5, 8 and 16) are in the excelsheet in the fields F3, F5 and F4 respectively.
The start value is always 1.
This process should end as soon it reaches the maximal value (here 16, means field F4)

[snb]

It means that your data do not match what your code suggested.
Please post a file.

[jonh]

Sub test()
    Dim v As Double
    v = 0.6
    addval 1, 5, 0.4, v
    addval 6, 8, 1.2, v
    addval 9, 16, 0.8, v
End Sub


Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
    For i = istart To iend
        ival = ival + incr
        Cells(i, 3) = ival
    Next
End Sub

[Polarfranz]

It means that your data do not match what your code suggested.
Please post a file.

Evaluation sheet04_02_macro1-25.xlsm

[Polarfranz]

jonh

Why do you putv=0.6? What is the effect of "v"?

[Polarfranz]

Sub test()
    Dim v As Double
    Dim a As Integer
    Dim b As Integer
    Dim secondlayer As Integer
    Dim secondlayerstep As Integer
    Dim secondtobottom As Integer
    Dim f As Integer
    Dim e As Integer
    a = Cells(8, 6).Value
    b = Cells(9, 6).Value
    f = Cells(3, 6).Value
    e = Cells(4, 6).Value
    secondlayer = Cells(5, 6).Value
    secondlayerstep = Cells(11, 6).Value
    secondtobottom = Cells(15, 6).Value
    
    v = 0.6
    addval 1, f, a, v
    addval 6, secondlayer, secondlayerstep, v
    addval 9, e, secondtobottom, v
End Sub
 
 
Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
    Dim I As Integer
    For I = istart To iend
        ival = ival + incr
        Cells(I, 3) = ival
    Next
End Sub

I tried it with this code, but then excel complains:
Compile error:
ByRef argument type mismatch. What does that mean?

[GTO]

It is a bit more complex than this, but in gist, ByRef means By Reference or that is, we are passing the variable back and forth when we pass 'incr as Double' in addval(). Thus, a, secondlayerstep and secondtobottom need to be doubles, or incr needs to be an Integer; but we cannot alter the Type of variable that is being passed into the called Sub or Function. Does that make sense?

Dim a As Double ' Integer
Dim b As Integer
Dim secondlayer As Integer
Dim secondlayerstep As Double ' Integer
Dim secondtobottom As Double ' Integer

Hope that helps,

Mark

[snb]

So klappt's:

Sub M_snb()
    For Each sh In Sheets
        sp = sh.Cells(1, 6).Resize(9)
        sn = sh.Cells(9, 1).CurrentRegion.Resize(, 3)
         
        For j = 2 To UBound(sn)
            sn(j, 3) = sn(j - 1, 3) + sp(8 - (sn(j - 1, 3) < sp(3, 1)), 1)
        Next
        
        sh.Cells(9, 1).CurrentRegion.Resize(, 3) = sn
    Next
End Sub

[Polarfranz]

snb:
Soll ich deinen Code mit in den anderen Code einbauen, oder den ganz allein dastehen lassen? Ich versteh leider nicht genau was dieser Code bewirkt

[snb]

The code is meant to replace your 'Versuch' totally.
It writes in column C your 'Z'- values.
Du kannst die Code laufen lassen und sehen ob der Erfolg dir gefällt.

PS. kennst du http://www.office-loesung.de/p/viewforum.php?f=166

[Polarfranz]

The code is meant to replace your 'Versuch' totally.
It writes in column C your 'Z'- values.
Du kannst die Code laufen lassen und sehen ob der Erfolg dir gefällt.

It looks almost solved.
Just the code takes for the first steps not the value F8 but F9 instead.
Then on the next increasement-step it adds the value from F8 but not from F11
And it is missing a third increasement step (starting value in F5) with the added value from F15

P.S. Hab ich noch nicht gekannt

[snb]

Then I think you'd better explain more in detail how results have to be calculated.(I tried to deduce it from your code)
But if you understand the logic & function of the code it should be rather simple to adapt it yourself.

[Polarfranz]

Then I think you'd better explain more in detail how results have to be calculated.(I tried to deduce it from your code)
But if you understand the logic & function of the code it should be rather simple to adapt it yourself.

I did not really understand the logic&function of the code you wrote... Otherwise it seems to be really handy.

The results should be calculated in the following way:

This it the raw-data:

1 0.549
2 0.741
3 0.741
4 0.741
5 1.106
6 12.597
7 52.680
8 118.524
9 196.830
10 286.526
11 372.597
12 428.750
13 458.827
14 482.285
15 482.285
16 482.285

I would like to recalculate the values for the first column and not have stepwidths of 1 all the time, but different changes in the increasement. As in the example above they shall look like the following (example):
From the values 1 to 5 the numbers are increased by 0.4, from higher than 5 to 8 by 1.2 and from 8 to the end by 0.8.
The new values shall be shown in the third column. Like this:

1 0.549 1
2 0.741 1.4
3 0.741 1.8
4 0.741 2.2
5 1.106 2.6
6 12.597 3.4
7 52.680 4.2
8 118.524 5
9 196.830 6.2
10 286.526 7.4
11 372.597 8.2
12 428.750 9.0
13 458.827 9.8
14 482.285 10.6
15 482.285 11.4
16 482.285 12.2

The values for the increasmentchange (here 0.4, 1.2 and 0.8) are given in the fileds F8, F11 and F15.
The thresholds for the increasement change (here 5 and 8) are given in the fileds F3 and F5.
This process shall continue until the 3rd column has as many entries as the first column(here 16). The endvalue 16 is given in the field F4.

I hope this is understandable for you

[jonh]

jonh

Why do you putv=0.6? What is the effect of "v"?

v=0.6 is the starting value minus the first increment.

.6+.4 = 1

As GTO said, the variable v (not just it's value), is passed between the procedures, so that it's value is updated and used as the output.

Const StartingValue As Byte = 1


Dim F8 As Range
Dim F11 As Range
Dim F15 As Range
Dim F3 As Range
Dim F5 As Range
Dim F4 As Range


Private Function validate() As Boolean
    Set F8 = Range("F8") '0.4
    Set F11 = Range("F11") '1.2
    Set F15 = Range("F15") '0.8
    Set F3 = Range("F3") '5
    Set F5 = Range("F5") '8
    Set F4 = Range("F4") '16
    
    'do any validating here
    
    validate = 1
End Function


Sub test()
    If Not validate Then Exit Sub
    
    Dim v As Double
    v = StartingValue - F8
    
    addval 1, CInt(F3), CDbl(F8), v
    addval CInt(F3) + 1, CInt(F5), CDbl(F11), v
    addval CInt(F5) + 1, CInt(F4), CDbl(F15), v
    
End Sub
 
Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
    For i = istart To iend
        ival = ival + incr
        Cells(i, 3) = ival
    Next
End Sub

[Polarfranz]

v=0.6 is the starting value minus the first increment.

.6+.4 = 1

As GTO said, the variable v (not just it's value), is passed between the procedures, so that it's value is updated and used as the output.

Const StartingValue As Byte = 1


Dim F8 As Range
Dim F11 As Range
Dim F15 As Range
Dim F3 As Range
Dim F5 As Range
Dim F4 As Range


Private Function validate() As Boolean
    Set F8 = Range("F8") '0.4
    Set F11 = Range("F11") '1.2
    Set F15 = Range("F15") '0.8
    Set F3 = Range("F3") '5
    Set F5 = Range("F5") '8
    Set F4 = Range("F4") '16
    
    'do any validating here
    
    validate = 1
End Function


Sub test()
    If Not validate Then Exit Sub
    
    Dim v As Double
    v = StartingValue - F8
    
    addval 1, CInt(F3), CDbl(F8), v
    addval CInt(F3) + 1, CInt(F5), CDbl(F11), v
    addval CInt(F5) + 1, CInt(F4), CDbl(F15), v
    
End Sub
 
Private Sub addval(istart As Integer, iend As Integer, incr As Double, ival)
    For i = istart To iend
        ival = ival + incr
        Cells(i, 3) = ival
    Next
End Sub

This looks like what I was looking for. Can you just tell me, how can I programme it, that excel starts writing the values in column C beginning in the 10th line? So far it starts writing the values in the C-column from the 1st line, but I would like to have it started from the 10th.
And what is the easiest way to run this code in every sheet?

Thanks a lot