Type Mismatch error

[saman1366]

Im making a double interpolation program for interpolating table values. It gets values from sheet1 in the excel sheet and uses them in an interpolation equation to get the interpolated value. The code is as follows..i get a "Type Mismatch error" and "X1 = [INDEX(X, MATCH(X,Sheet1!B2:B9, TRUE),1)]" comes up as yellow.

Private Sub CommandButton1_Click()
'Creating variables
Dim X As Double
Dim Y As Double
Dim X1 As Double
Dim X2 As Double
Dim Y1 As Double
Dim Y2 As Double
Dim Q11 As Double
Dim Q12 As Double
Dim Q21 As Double
Dim Q22 As Double
Dim P As Double
'assigning numbers to variables
X = CDbl(txtX.Value)
Y = CDbl(txtY.Value)
'Checking for excat matches
If IsError([VLOOKUP(X, Sheet1!B2:B9, 1, False)]) And IsError([HLOOKUP(Y, Sheet1!B2:I9, 1, False)]) Then
'if no exact matches are found then:
X1 = [INDEX(X, MATCH(X,Sheet1!B2:B9, TRUE),1)]
X2 = [INDEX(Sheet1!B2:B9, MATCH(X,Sheet1!B2:B9,TRUE)+1, 1)]
Y1 = [INDEX(Y, MATCH(Y, 1, Sheet1!B2:I2), True)]
Y2 = [INDEX(Sheet1!B2:I2, 1, MATCH(Y,Sheet1!B2:I2,TRUE)+1)]
Q11 = [INDEX(Sheet1!B2:I9, MATCH(X1,Sheet1!B2:B9,FALSE), MATCH(Y1,Sheet1!B2:I2,FALSE))]
Q12 = [INDEX(Sheet1!B2:I9, MATCH(X1,Sheet1!B2:B9,FALSE), MATCH(Y2,Sheet1!B2:I2,FALSE))]
Q21 = [INDEX(Sheet1!B2:I9, MATCH(X2,Sheet1!B2:B9,FALSE), MATCH(Y1,Sheet1!B2:I2,FALSE))]
Q22 = [INDEX(Sheet1!B2:I9, MATCH(X2,Sheet1!B2:B9,FALSE), MATCH(Y2,Sheet1!B2:I2,FALSE))]

'calculating
P = (X2 - X) * (Y2 - Y) * Q11 / ((X2 - X1) * (Y2 - Y1)) + (X - X1) * (Y2 - Y) * Q11 / ((X2 - X1) * (Y2 - Y1)) + (X2 - X) * (Y - Y1) * Q12 / ((X2 - X1) * (Y2 - Y1)) + (X - X1) * (Y - Y1) * Q22 / ((X2 - X1) * (Y2 - Y1))

txtP.Text = CStr(P)

Else

P = [INDEX(Sheet1!B2:I9, MATCH(X,Sheet1!B2:B9,FALSE), MATCH(Y,Sheet1!B2:I2,FALSE))]
txtP.Text = CStr(P)

End If

End Sub

Your help will be greatly appreciated.

 

[macropod]

You have X and X1 dimmed as doubles. Are you sure what's being retuirned qualifies (eg neither Strings nor large Longs)?

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

hi,

INDEX() function requies an ARRAY as the first argument. Your code has X as NOT an array.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}