与预测函数的非线性插值

Question

我想在列中进行特定数字的非线性插值，并在第二列中获取等效数字。它给我带来了列表分隔符或(

的编译器错误

 Sub interp()
 Dim temp As Integer
 Dim var As Integer
 Dim var1 As Integer
 Dim xs As Range
 Dim ys As Range
 Set xs = Sheets(1).Range("A8:A19")
 Set ys = Sheets(1).Range("B8:B19")
 temp = Sheets(1).Range("E3").Value
 Dim indexxs As Integer
 Dim indexys As Integer
 Dim indexxs1 As Integer
 Dim indexys1 As Integer

 var = WorksheetFunction.Match(temp, xs, 1)
 var1 = WorksheetFunction.Match(temp, xs, 1) + 1

 indexxs = Application.WorksheetFunction.Index(xs, var)
 indexxs1 = Application.WorksheetFunction.Index(xs, var1)
 indexys = Application.WorksheetFunction.Index(ys, var)
 indexys1 = Application.WorksheetFunction.Index(ys, var1)


 Sheets(1).Range("g3").Value = Application.WorksheetFunction.Forecast(temp,indexys:indexys1,indexxs:indexxs1)

End Sub

Answer 1

forcast想要一个范围或一个数组，而不是数字，这是您使用 indexys:indexys1传递的数字，您正在传递索引的值，因此它将返回类似104:34而不是范围或范围或阵列。

您可以将前播线更改为：

Sheets(1).Range("g3").value = Application.Forecast(temp, Array(indexys, indexys1), Array(indexxs, indexxs1))

它将起作用，但是有更好，更快的方法可以使用VBA而不是工作表函数获得所需的结果。

因此，我们可以摆脱索引并创建值的数组。然后在公式中使用这些数组。

 Sub interp()
 Dim temp As Long
 Dim var As Long
 Dim var1 As Long
 Dim xs As Range
 Dim ys As Range
 With Worksheets(1)
     Set xs = .Range("A8:A19")
     Set ys = .Range("B8:B19")
     temp = .Range("E3").value

    var = WorksheetFunction.Match(temp, xs, 1)
    var1 = var + 1

     Dim y As Variant
     y = .Range(ys.Cells(var, 1), ys.Cells(var1, 1)).value
     Dim x As Variant
     x = .Range(xs.Cells(var, 1), xs.Cells(var1, 1)).value

    .Range("g3").value = Application.WorksheetFunction.Forecast(temp, y, x)
End With
End Sub