根据相关值设置等于其他单元格的单元格

Question

我试着根据它们在时间上的紧密程度，在不同的时间里把它们联系起来。在两个不同的表(“质量平衡”和“平均值”)中有一个时间列，我想复制数字从“平均数”粘贴到“质量平衡”，根据这些数字的时间是多么接近(见图)。

我遇到麻烦的部分是最后的If声明。出于某种原因，它将最终值从“平均值”粘贴到“质量平衡”中的所有行中，而不是粘贴时间最近的值。

变量描述：

mbv1 &2是“质量平衡”的最初和最后一次。

mbd1 &2是“质量平衡”中mbv1 &2的初始行和最后行。

avgd1 &2是“平均数”中的初始和最后一次。

​

​

Dim c1 As Double
For o = mbv1 To mbv2
    For n = mbd1 To mbd2
        For m = avgd1 To avgd2
            For i = 0 To 40
                If Abs(Cells(m + i, 1) - o) < Abs(Cells(m + i + 1, 1) - o) Then
                    c1 = i + m
                    Sheets("Mass Balance").Cells(n, 3) = Sheets("Averages").Cells(c1, 4)
                    Sheets("Mass Balance").Cells(n, 10) = Sheets("Averages").Cells(c1, 6)
                Else
                End If
            Next
        Next
    Next
Next

Answer 1

我创建了一个示例工作表，如下所示：

 |         A         |         B          | C |       D      |       E       |
-------------------------------------------------------------------------------------------
1| Mass Balance Data | Mass Balance Times |   | Average Data | Average Times |
-------------------------------------------------------------------------------------------
2|                   |         13         |   |       1      |       10      |
3|                   |         22         |   |       2      |       20      |
4|                   |         31         |   |       3      |       30      |
5|                   |        ...         |   |     ...      |      ...      |

由于下面的代码是使用简单的数据完成的，在一张纸中，您将不得不为您自己的代码更改一些内容，但是在上面的数据上它对我起了作用。我们的想法是，总共有两个循环。你循环通过每一个质量平衡时间。对于每一个质量平衡时间循环，通过整个平均时间列。找到与质量平衡时间最接近的时间后，复制数据值并将其粘贴到该特定时间的质量平衡数据中。

Option Explicit

Sub dataCollect()

'MBr stands for Mass Balance Row
'Each of these represent the row and col of a cell with time values
Dim MBr, MBc As Integer
Dim AVGr, AVGc As Integer
Dim minRow, minCol As Integer

Dim minDiff As Integer
Dim mbTime As Integer
Dim avgTime As Integer

Dim currDiff As Integer

'start off with beginning row and column of mass balance times and average times
MBc = 2
AVGc = 5
For MBr = 2 To 10

    'set minDiff to be a really high number
    'so that the first difference value between average and mass balance times
    'will be saved as the minimum difference value (used for if statement later)
    minDiff = 10000

    'loop through each average time
    'find and save the row and column of the cell with the closest time to mass balance time
    For AVGr = 2 To 10

        'mass balance time value
        mbTime = Cells(MBr, MBc).Value

        'average time value
        avgTime = Cells(AVGr, AVGc).Value

        'set current difference value
        currDiff = Abs(avgTime - mbTime)

        'if the difference in times is smaller than the last recorded difference
        'then save the row and column of the current average time cell
        'and set the new minimum (minDiff) value to be the current diff (currDiff) value
        If (currDiff < minDiff) Then
            minRow = AVGr
            minCol = AVGc
            minDiff = currDiff
        End If
    Next

    'now set the mass balance value to be the average value with the closest time
    'minCol - 1 and MBc -1     grabs the data value assosiated with that time
    Cells(MBr, MBc - 1) = Cells(minRow, minCol - 1).Value
Next
End Sub