文章/答案/技术大牛

发布

社区首页 >问答首页 >如何使用非连续范围作为excel函数的参数

问如何使用非连续范围作为excel函数的参数
EN

Stack Overflow用户

提问于 2021-04-25 18:14:09

回答 1查看 232关注 0票数 0

我有一个令人沮丧的问题，使用非连续范围作为excel函数的参数(如斜率(range_y，range_x))，我应该如何做呢？！我试过为范围定义名称或把范围放在全篇文章中。但是它不起作用，我遇到了#值错误。我也尝试过间接功能，但没有取得成功。即使在vba，我也解决不了这个问题。请帮帮我。这是我的代码：

Sub Do_Interpolate_Extrapolate()
Dim X_range,Y_range As Range
Dim a_TestCell As Range
Dim FStr as String
Dim Lin_a as double  
....
Set X_range = Union(Range("V6:V7"), Range("V9:V12"), Range("V14"), Range("V17:V18"))
Set Y_range = Union(Range("T6:T7"), Range("T9:T12"), Range("T14"), Range("T17:T18"))
Set a_TestCell =Range("A2")
....
FStr = "=SLOPE(" & Y_range.Address & "," & X_range.Address & ")"
a_TestCell.Formula = FStr
Lin_a = CDbl(a_TestCell.Value)
a_TestCell.ClearContents
MsgBox "Lin_a =" & Lin_a
....
End sub

运行这段代码，我得到运行时错误'1004‘，表示应用程序定义的或对象定义的错误！

excel

vba

function

parameters

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-04-25 19:56:50

请试试下一个代码。Slope需要连续的范围。返回不连续的范围地址，就像其用逗号分隔的区域一样，公式将每个区域解释为参数，“查找”太多：

Sub Do_Interpolate_Extrapolate_Array()
 Dim X_range As Range, Y_range As Range
 Dim Lin_a As Double, colNo As Long, arrX, arrY
 Dim tempRngX As Range, tempRngY As Range 'temporary ranges to be created and used to calculate the slope

 colNo = ActiveSheet.UsedRange.Columns.count  'the sheet existing number of columns
 Set X_range = Union(Range("V6:V7"), Range("V9:V12"), Range("V14"), Range("V17:V18"))
 Set Y_range = Union(Range("T6:T7"), Range("T9:T12"), Range("T14"), Range("T17:T18"))

 arrX = contArrayFromDscRng(X_range) 'put the content of the discontinuous range in a continuous array
 arrY = contArrayFromDscRng(Y_range) 'put the content of the discontinuous range in a continuous array
 
 cells(1, colNo + 1).Resize(UBound(arrX), 1).value = arrX 'drop the X array in the first column after the last
 cells(1, colNo + 2).Resize(UBound(arrY), 1).value = arrY 'drop the Y array in the second column after the last

 
 Set tempRngX = Range(cells(1, colNo + 1), cells(UBound(arrX), colNo + 1)) 'set the temporary X range
 Set tempRngY = Range(cells(1, colNo + 2), cells(UBound(arrY), colNo + 2)) 'set the temporary Y range

 Lin_a = WorksheetFunction.Slope(tempRngX, tempRngY) 'calculate the Slope using the temporary ranges

 tempRngX.Clear: tempRngY.Clear 'clear the temporary ranges
 MsgBox "Lin_a =" & Lin_a       'return the calculated Slope
End Sub

Private Function contArrayFromDscRng(rng As Range) As Variant 'makes an array from a discontinuous range
    Dim a As Range, arr, count As Long, i As Long
    
    ReDim arr(1 To rng.cells.count, 1 To 1): count = 1
    For Each a In rng.Areas
            For i = 1 To a.cells.count
                arr(count, 1) = a.cells(i).value: count = count + 1
            Next
    Next
    contArrayFromDscRng = arr
End Function

编辑的

使用(仅)数组的变体(感谢Chris和他在函数中使用Value2而不是Value )的想法：

Sub Do_Interpolate_Extrapolate_Array2()
 Dim X_range As Range, Y_range As Range
 Dim Lin_a As Double, colNo As Long, arrX, arrY

 colNo = ActiveSheet.UsedRange.Columns.count  'the sheet existing number of columns
 Set X_range = Union(Range("V6:V7"), Range("V9:V12"), Range("V14"), Range("V17:V18"))
 Set Y_range = Union(Range("T6:T7"), Range("T9:T12"), Range("T14"), Range("T17:T18"))
 
 arrX = contArrayFromDscRng(X_range) 'put the content of the discontinuous range in a continuous array
 arrY = contArrayFromDscRng(Y_range) 'put the content of the discontinuous range in a continuous array
 
 Lin_a = WorksheetFunction.Slope(arrX, arrY) 'calculate the slope using arrays
 
 MsgBox "Lin_a =" & Lin_a                    'return the calculated Slope
End Sub

更快，更紧凑，更好..。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/67256761

复制

相似问题

问如何使用非连续范围作为excel函数的参数
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用非连续范围作为excel函数的参数EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用非连续范围作为excel函数的参数
EN