Calculations Incorrect & Error

I did the caculations of the grid and I'm getting weird resutls.
I press the calc button and numbers appear but the calculations are incorrect
and the numbers appear in the column heading.

I get an run-time error 381 Subscript out of range.

I've been using code like the below to calc the columns

The calculations are returning numbers like I
1.11794274231176e+116 and the column heading changed to 25 from a column label.

Why is it that If I keep pressing the calc button the calc changes?

VB Code:

With MSFlexGrid1
     .Redraw = False
     For x = 0 To .Rows - 1
         .TextMatrix(x, 5) = (Val(.TextMatrix(x, 9)) * 6.5) + 0.1 * (Val(.TextMatrix(x, 10)))
          Next
       .Redraw = True
        End With

Please help. Thanks!

Hi again cheyenne

VB Code:

With MSFlexGrid1
     .Redraw = False
     'Start loop from 1 if u have a fixed row for headings
     For x = 1 To .Rows - 1
          'you can format results as necessary
         .TextMatrix(x, 5) = Format$((Val(.TextMatrix(x, 9)) * 6.5) _
            + 0.1 * (Val(.TextMatrix(x, 10))),"0.00")
      Next
     .Redraw = True
End With

regards
Stuart

Hey

So why I'm getting subsccript out of range?

Also when I first start up and make no changes and press the calc button
new numbers appear. I would expect nothing to change since I made
no changes. Every time I press teh calc button the numbers are calc
to different numbers. Why?
:confused: :confused: :confused:

VB Code:

With MSFlexGrid1
     .Redraw = False
     For x = 1 To .Rows - 1
          'calculating the KLOC, KLOC = FP * Language / 1000
       .TextMatrix(x, 3) = Format$(((Val(.TextMatrix(x, 5)) _
      * Val(.TextMatrix(x, 13))) / 1000), "0.00")
          Next
       .Redraw = True
        End With

I think it doesn't like the column 13, but I dont know why those numbers are only 2 digits.

Hi
Ok i suggest that u put a STOP statement in ur calc event. Step thru it with F8 and see which rows are causing problems. Have u got blank rows? U may have to test for this. Are the answers in rows always based on the fields in the same row? Sounds like u have a recursion error where one calc depends on another.
eg X = Y + 3, Y = X + 4 is recursion
Regards
Stuart

I think I have an recursion problem. So what can I do to fix it.
The code I'm trying to use is below.

I'm still have the problem of subsccript out of range?


[Highlight=VB]

With MSFlexGrid1
.Redraw = False
For x = 1 To .Rows - 1 ' start from row 1 since row 0 is headings
.TextMatrix(x, 5) = Format$((Val(.TextMatrix(x, 9)) * 6.5) _
+ 0.1 * (Val(.TextMatrix(x, 10))), "0.00")
Next
.Redraw = True
End With
With MSFlexGrid1
.Redraw = False
For x = 1 To .Rows - 1 ' start from row 1 since row 0 is headings

.TextMatrix(x, 3) = Format$(((Val(.TextMatrix(x, 5)) _
* Val(.TextMatrix(x, 13))) / 1000), "0.00") Error: SubScript Out of Range
Next
.Redraw = True
End With

With MSFlexGrid1
.Redraw = False
For x = 1 To .Rows - 1 ' start from row 1 since row 0 is headings
.TextMatrix(x, 1) = Format$((2.4 * Val(.TextMatrix(x, 4)) _
^ Val(.TextMatrix(x, 11)) * Val(.TextMatrix(x, 8))), "0.00")
Next
.Redraw = True
End With

With MSFlexGrid1
.Redraw = False
For x = 1 To .Rows - 1 ' start from row 1 since row 0 is headings
.TextMatrix(x, 0) = Format$((2.5 * Val(.TextMatrix(x, 1)) _
^ Val(.TextMatrix(x, 12))), "0.00")
Next
.Redraw = True
End With

With MSFlexGrid1
.Redraw = False
For x = 1 To .Rows - 1 ' start from row 1 since row 0 is headings
.TextMatrix(x, 4) = Format$((.TextMatrix(x, 2)) * Val(.TextMatrix(x, 1)), "0.00")
Next
.Redraw = True
End With

End Sub
[Highlight=VB]

Hi
1) Subscript out of range may be becos u are referring to a non existent column. U make a reference to column 13 so u must have Msflexgrid1.Cols = 14 (which is 0 to 13)
2) I dont know ur loaded data but are u sure that ur exponential formula do not produce crazily high numbers.
3) I see where ur recursion error is.
In loop 3, column 1 is dependant on the value in col 4
In loop 5, column 4 is dependant on the value in col 1
eg

VB Code:

[b].TextMatrix(x, 1)[/b] = Format$((2.4 * Val([b].TextMatrix(x, 4)[/b]) _ 
^ Val(.TextMatrix(x, 11)) * Val(.TextMatrix(x, 8))), "0.00") 
 
[b].TextMatrix(x, 4)[/b] = Format$((.TextMatrix(x, 2)) * Val([b].TextMatrix(x, 1)[/b]), "0.00")

4) Also, the code is getting a bit messy becos of all the val's formats etc so i added a function to reduce the code

VB Code:

Private Sub Command1_Click()
    With MSFlexGrid1
        .Redraw = False
        For x = 1 To .Rows - 1 ' start from row 1 since row 0 is headings
            .TextMatrix(x, 5) = Format$(TVal(x, 9) * 6.5 + 0.1 * TVal(x, 10), "0.00")
            .TextMatrix(x, 3) = Format$(TVal(x, 5) * TVal(x, 13) / 1000, "0.00")
            .TextMatrix(x, 1) = Format$(2.4 * TVal(x, 4) ^ TVal(x, 11) * TVal(x, 8), "0.00")
            .TextMatrix(x, 0) = Format$(2.5 * TVal(x, 1) ^ TVal(x, 12), "0.00")
            .TextMatrix(x, 4) = Format$(TVal(x, 2) * TVal(x, 1), "0.00")
        Next
        .Redraw = True
    End With
End Sub
 
Private Function TVal(llngRow As Long, llngCol As Long) As Double
    TVal = Val(MSFlexGrid1.TextMatrix(llngRow, llngCol))
End Function

That code still includes the recursion error but i am afraid that that error is up to you becos only u know what answers u want in each column.
Regards
Stuart

Hey

I've got another question :(

When the grid is display and filled with the data from the database and I press the calc
button I would expect nothiing to change in the col and rows,however the opposite occurs.
The numbers are re-generated and I get different numbers.

In other words if I press the calc button without pressing/editing any rows or cols
the numbers are re-calculated and I get different numbers.
I would expect the numbers to remain the same as they were when form
was loaded since no edits were the same.

Is it supposed to work that way?
How do I fix that problem/issue? I would want the numbers to remain the same
until edits are generated and a calculations really need to be done.

I guess one way to solve it would be to not enable the calc button until a edit
has occured. What is the simpliest way to that? What is the way to determine
if the edit has occured:?

Thanks again:confused:

Hi
Lol this project is getting bigger than Ben Hur :D i take it that the database does not store the results of your formula? or does it? if it doesnt store the results of the formula then obviously u would have to call the calculation subroutine immediately after loading the data. If the formula results are stored in the db then there should be no change unless you still have a recursion problem. I think that u are going to have to organise the flow of events on paper or something to make sure that each event is happening when u want it to.
regards
Stuart

The grid is a place that shows the data and the user can edit ( if i get it working).
THe data is the result of running/going thru the application and generating
numbers. The numbers are generated based upon user responses to questions
and the calucations/formulas are runned and saved in the database.

Hello,

I'm still stuck on this problem.
If I press the calculation button without doing edits
it re-calculate the values and they are differenct. If there
aren't any changes the numbers shouldn't change.

Also If I put all zeroes in the values and then enter
new numbers in the calcuations are incorrect.

Do the () 's matter inthe above code. Do I need more ( ) () or what?

THanks for the help:confused: :confused: