help plz in sorting out the error in problem

[nitn28]

getting error ==== Run Time Error"13"..
Mismatch..

hi everyone here is my code i m getting error [Run Time Error"13"..
Mismatch..] while exceuting this code

dont knw wats wrong

i m trying to caculate are under curve for "3" points if i enter (have
data set of around 1095 points)

j = 1 to 1092 then its working fine
but i want to mak this programe running for any data set
plz suggest to mak this code "more flexible to use"

Sub Button1_Click()

RowcountA = Cells(Rows.Count, "a").End(xlUp).Row


For j = 1 To RowcountA Step 3

x0 = Cells(j + 2, "a").Value
xn = Cells(j + 4, "a").Value

Cells(j + 2, "g").Value = x0
Cells(j + 2, "h").Value = xn

Cells(j + 2, "D").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-2]:R[2]C[-2],RC[-3]:R[2]C[-3]^{1,2}),1)"
Cells(j + 2, "E").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-3]:R[2]C[-3],RC[-4]:R[2]C[-4]^{1,2}),1,2)"
Cells(j + 2, "F").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-4]:R[2]C[-4],RC[-5]:R[2]C[-5]^{1,2}),1,3)"

p = Cells(j + 2, "d").Value
q = Cells(j + 2, "e").Value
r = Cells(j + 2, "f").Value

m = 100
n = m / 2
D = (xn - x0) / m

x = x0
y = p * x ^ 2 + q * x + r
Integ = y
Range("h4") = xn
x = xn
y = p * x ^ 2 + q * x + r
Integ = Integ + y

'[2] This loop adds terms 4*(y(1)+y(3)+ ... + y(2*n-1))
For i = 1 To 2 * n - 1 Step 2
x = x0 + i * D
y = p * x ^ 2 + q * x + r
Integ = Integ + 4 * y
Next i
'[3] This loop adds tersm 2*(y(2)+y(4)+...+y(2*n-2))
For i = 2 To 2 * n - 2 Step 2
x = x0 + i * D
y = p * x ^ 2 + q * x + r
Integ = Integ + 2 * y
Next i
'[4] The sum contained in "Integ" is now multiplied by Dx/3 to
finish calculation
Integ = Integ * D / 3
Cells(j + 2, "k") = Integ

' code to average depths

'Cells(j + 2, "L").Select
'ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-9]:R[2]C[-9])"

' code to get volume

' Cells(j + 2, "m").Select
' ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-2],RC[-1])"

Next j


End Sub

many thanx in advance

 

[nitn28]

hi everyonw still waiting for ur replys

is this problem too difficult or i havnt framed properly plz let me
knw

many thanks

 

[Jerry W. Lewis]

You simultaneously gave too much and not enough information for a quick
response. By giving the entire code instead of distilling it to where the
problem occurs, you forced responders to try to reproduce your working
environment, yet you do not provide example data to simplify that task.

The issue appears to be that your outer loop executes one too many times,
with the error being produced when LINEST is used with references to empty
cells. Change your outer loop to
For j = 1 To RowcountA - 2 Step 3
and it should work as you expect.

In terms of the overall code, you seem to be working very hard to do
something fairly simple. Analytic integration of quadratics is
straightforward, and should be more accurate than summing a large number of
slices. Why not simplify to

Sub Button1_Click()

RowcountA = Cells(Rows.Count, "a").End(xlUp).Row

For j = 1 To RowcountA - 2 Step 3

Cells(j + 2, "g").FormulaR1C1 = "=RC[-6]"
Cells(j + 2, "h").FormulaR1C1 = "=R[2]C[-7]"

Range(Cells(j + 2, "D"), Cells(j + 2, "F")).FormulaArray = _
"=LINEST(RC[-2]:R[2]C[-2],RC[-3]:R[2]C[-3]^{1,2})"

[H4].Formula = "=" & Cells(j + 4, "a").Address
Cells(j + 2, "k").FormulaR1C1 = _

"=(RC[-3]-RC[-4])*(RC[-7]*((RC[-3]-RC[-4])^2/3+RC[-3]*RC[-4])+RC[-6]*(RC[-3]+RC[-4])/2+RC[-5])"

Next j

End Sub

Or skip the macro entirely, since everything can be done in simple cell
formulas that can be copied down over the range of data.

Jerry

getting error ==== Run Time Error"13"..
Mismatch..

hi everyone here is my code i m getting error [Run Time Error"13"..
Mismatch..] while exceuting this code

dont knw wats wrong

i m trying to caculate are under curve for "3" points if i enter (have
data set of around 1095 points)

j = 1 to 1092 then its working fine
but i want to mak this programe running for any data set
plz suggest to mak this code "more flexible to use"

Sub Button1_Click()

RowcountA = Cells(Rows.Count, "a").End(xlUp).Row


For j = 1 To RowcountA Step 3

x0 = Cells(j + 2, "a").Value
xn = Cells(j + 4, "a").Value

Cells(j + 2, "g").Value = x0
Cells(j + 2, "h").Value = xn

Cells(j + 2, "D").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-2]:R[2]C[-2],RC[-3]:R[2]C[-3]^{1,2}),1)"
Cells(j + 2, "E").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-3]:R[2]C[-3],RC[-4]:R[2]C[-4]^{1,2}),1,2)"
Cells(j + 2, "F").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(LINEST(RC[-4]:R[2]C[-4],RC[-5]:R[2]C[-5]^{1,2}),1,3)"

p = Cells(j + 2, "d").Value
q = Cells(j + 2, "e").Value
r = Cells(j + 2, "f").Value

m = 100
n = m / 2
D = (xn - x0) / m

x = x0
y = p * x ^ 2 + q * x + r
Integ = y
Range("h4") = xn
x = xn
y = p * x ^ 2 + q * x + r
Integ = Integ + y

'[2] This loop adds terms 4*(y(1)+y(3)+ ... + y(2*n-1))
For i = 1 To 2 * n - 1 Step 2
x = x0 + i * D
y = p * x ^ 2 + q * x + r
Integ = Integ + 4 * y
Next i
'[3] This loop adds tersm 2*(y(2)+y(4)+...+y(2*n-2))
For i = 2 To 2 * n - 2 Step 2
x = x0 + i * D
y = p * x ^ 2 + q * x + r
Integ = Integ + 2 * y
Next i
'[4] The sum contained in "Integ" is now multiplied by Dx/3 to
finish calculation
Integ = Integ * D / 3
Cells(j + 2, "k") = Integ

' code to average depths

'Cells(j + 2, "L").Select
'ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-9]:R[2]C[-9])"

' code to get volume

' Cells(j + 2, "m").Select
' ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-2],RC[-1])"

Next j


End Sub

many thanx in advance