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

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.


filo666 wrote:

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