help plz in sorting out the error in problem

N

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
 
N

nitn28

hi everyonw still waiting for ur replys

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

many thanks
 
G

Guest

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

[email protected] said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Time Error"13".. Mismatch..help plz 2
Trendline Coeeficients Vba Procedure 3
VBa code for index(linest) 2
Scrabble Value calculation for Welsh words 0
Formula appearing instead of result 2
Permutations andText to column problem 6
Help with my VBA and formula 8
Array Sorting problem 4

Top