=?Utf-8?Q?a_With_missing_and_I_don=C2=B4t_know_?==?Utf-8?Q?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.


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