使用动态数组VBA确定斜率和截距

Its been a while since I've used arrays in Excel VBA so please forgive me...

我在Excel VBA中使用数组已经有一段时间了，请原谅我……

I'm trying to define a dynamic array based consecutive matching cells that are determined in a loop. I'm sure my syntax is wrong for defining the arrays I'm just not sure how. The difficulty is that my array consists of about 6 consecutive rows in 1 column, plus another cell in a different column. Any ideas?

我正在尝试定义一个基于动态数组的连续匹配单元格，它们是在循环中确定的。我确定我的语法定义数组是错误的，只是我不知道如何定义。难点在于，我的数组由一个列中大约6个连续的行，加上另一个列中的单元格。什么好主意吗?

Sub calib_range()
Dim instrument As Variant
Dim calibrator As Variant
Dim lastrow As Integer

lastrow = ThisWorkbook.ActiveSheet.Range("b2").SpecialCells(xlCellTypeLastCell).Row

For i = 4 To lastrow

If Cells(i, 4) Like "MPC*" Then
'enter loop to determine length of MPC* array
  For x = i + 1 To lastrow
    If Cells(x, 4) = Cells(x - 1, 4) Then
      Else
      x = x - 1
      Exit For
    End If
  Next x

  instrument = Array(Cells(i, 17), Range(Cells(i, 14), Cells(x, 14)))
  calibrator = Array(0, Range(Cells(i, 12), Cells(x, 12)))
  Slope = Application.WorksheetFunction.Slope(instrument, calibrator)
  Intercept = Application.WorksheetFunction.Intercept(instrument, calibrator)
  Cells(i, 22) = Slope
  Cells(i, 23) = Intercept
End If
Next i

End Sub

1 个解决方案

#1

Your problem is here:

你的问题是:

  calibrator = Array(0, Range(Cells(i, 12), Cells(x, 12)))

You are not allowed to do so, because VBA thinks that in your array you get a 0 and a range. Thus, your array consists of two different types of valuse. Which is not what you need.

不允许这样做，因为VBA认为在数组中会有一个0和一个范围。因此，数组由两种不同类型的valuse组成。这不是你需要的。

Read here a little more about how to initialize arrays, it is well explained.

在这里再多读一些关于如何初始化数组的内容，会得到很好的解释。

Edit: Also on the previous line you simply make an array of ranges. What would work out for you is probably something like this:

编辑:在前一行中，你只需要创建一个范围数组。对你来说可能是这样的:

Public Sub CheckArray()

    Dim my_array()   As Double

    ReDim my_array(6)
    my_array(0) = Cells(1, 17)
    my_array(1) = Cells(2, 17)
    my_array(2) = Cells(3, 17)
    my_array(3) = Cells(4, 17)
    my_array(4) = Cells(5, 17)
    my_array(5) = Cells(6, 17)
    my_array(6) = Cells(7, 17)


End Sub

#1