a With missing and I don´t know where and how to put it

[filo666]

I, I have this macro that look for a value between 2 numbers in column x and
the interpolate for a given value, the macro is:

Sub inter()
Dim Value
Dim a, P As Integer
Dim XRange As Range
Dim YRange As Range

Value = 5.5 'value given by the user (could be textbox1)
a = 1
Set XRange = Range("a1:a600")
Set YRange = Range("b1:b600")
While a < XRange.Rows.Count
If Value > Cells(a, 1) And Value < Cells(a + 1, 1) Then
P = a
Xs = Array(.Index(XRange.Value2, P, 1), .Index(XRange.Value2, P + 1,
1))
Ys = Array(.Index(YRange.Value2, P, 1), .Index(YRange.Value2, P
+ 1, 1))
Interpolate = .Forecast(Value, Ys, Xs)
MsgBox Interpolate
Else
a = a + 1
End If
Wend
End Sub

I know that those .Index and .Forecast needs a "with" and "end with"
but I don't know what kind of with (I thought With activeworksheet but it
didn't work)
so could someone tell me how to make my macro run????
TIA

 

[Dave Peterson]

Maybe...

Option Explicit
Sub inter()
Dim myValue As Variant
Dim a As Long
Dim P As Long
Dim XRange As Range
Dim YRange As Range
Dim Xs As Variant
Dim Ys As Variant
Dim Interpolate As Double

myValue = 5.5 'myValue given by the user (could be textbox1)
a = 1
Set XRange = Range("a1:a600")
Set YRange = Range("b1:b600")
While a < XRange.Rows.Count
If myValue > Cells(a, 1) And myValue < Cells(a + 1, 1) Then
P = a
With Application
Xs = Array(.Index(XRange.Value2, P, 1), _
.Index(XRange.Value2, P + 1, 1))
Ys = Array(.Index(YRange.Value2, P, 1), _
.Index(YRange.Value2, P + 1, 1))
Interpolate = .Forecast(myValue, Ys, Xs)
End With
MsgBox Interpolate
Else
a = a + 1
End If
Wend
End Sub

I wouldn't use a variable by the name of Value. It may not confuse excel, but
it sure would confuse me.