在另一个数组的基础上选择值 [英] Selecting values in one array based on another

问题描述

之前我已经在VBA中做了一些相对简单的事情，但是我认为对于这个项目，我需要进入使用变量和数组的工作，这似乎是我目前无法实现的.

I've done some relatively straightforward things in VBA before but I think for this project I need to move into using variables and possibly arrays, which seem to be just beyond my reach at the moment.

我有4列数据:col.A是开始时间，col.B是相应的结束时间(每个大约30)，col.C是数据点的时间戳(在A和B列中指定的时间段之内和之间收集；这些列中有40,000多行数据)和col.D是在col中引用的每个时间点观察到的数据.C.每个文件都有不同的开始/结束时间，因此我想创建一个可以从单元格读取它们的宏.

I've got 4 columns of data: col. A is start times, col. B is the corresponding end times (there will be about 30 of each), col. C is time-stamps for the data points (collected during and between the time periods specified in columns A and B; there are 40,000+ lines of data in these columns), and col. D is the data observed at each time point referenced in col. C. Each file will have different start/end times, so I want to make a macro that can read them from the cells.

我需要将A和B列中指定的每个时间段的时间戳和数据点放入单独的列中(例如，时间段1的数据将在F和G列中，时间段2的数据将在H和I中，依此类推).因此，我想编写一个本质上将搜索col的宏.C表示介于第一个时间段的开始时间和结束时间之间的值，然后将相关的值复制/粘贴到相应的新列中.

I need to put the time-stamp and data points from each time period specified in columns A and B, into separate columns (so, for example, time period 1 data will be in columns F and G, time period 2 data will be in H and I, and so on). So, I want to write a macro that will essentially search col. C for values that fall between the start and end times of the first time period, and copy/paste the relevant ones into the appropriate new columns.

我一直在疯狂搜索，但是我很难组合各种可以解决不同步骤的代码.到目前为止，这就是我所拥有的(以及一些注释，这些内容说明了我认为应该做的事情)

I've been googling like crazy but I'm having a hard time putting together various pieces of code that can address the different steps. This is what I have so far (along with some notes saying what I think things are supposed to be doing):

Sub CopyRows2()

Dim endTime As Range, startTime As Range
Dim copyRange As Range, lastRow As Range, timePoint As Range
Dim i As Long, k As Long

    Set startTime = ActiveSheet.Cells(i, 2).Value
    lastRow = ActiveSheet.Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row  'find the last row of the time periods
    Set timePoint = ActiveSheet.Cells(2, 3)  'start looking for times in cell C2
  Do Until lastRow = ""
    For i = 2 To lastRow
       Set endTime = startTime.Offset(0, 1)   'identify the end of the time period
            If timePoint.Value >= startTime.Value Then    'find the row with the first data point in the time period
                If copyRange Is Nothing Then   'this "copyRange" stuff is based on: http://stackoverflow.com/questions/9790924/excel-vba-how-to-select-rows-based-on-data-in-a-column
                    Set copyRange = ActiveSheet.Rows(i)
                Else
                   Set copyRange = Union(copyRange, ActiveSheet.Rows(1))
                End If
            End If
        Next i
    If Not copyRange Is Nothing Then
        ActiveSheet.copyRange.Copy ActiveSheet.Cells(2, k)  'k is meant to be the column number, which will keep incrementing by 2 but I don't know how to tell it to do that
       End If
    Loop
End Sub

现在它给我一个错误:

应用程序定义的错误或对象定义的错误"

"Application-defined or object-defined error"

在这一行:

Set startTime = ActiveSheet.Cells(i, 1).Value 

我不知道为什么.但是，我非常确定它存在较大的问题，即使我解决了导致错误的问题，它也可能实际上不会按照我的意愿去做.

And I can't figure out why. But, I'm pretty sure that there are larger problems with it and it probably won't actually do what I'm trying to get it to do, even if I fix the problem that's causing the error.

目前，我希望有人可以提供帮助的具体事项是:

At the moment the specific things I hope someone might be able to help with are:

1. 是什么原因导致错误?

1. What is causing the error?

我如何定义k使其递增2(请参见上面的代码中的注释)

How do I define k so that it will increment by 2 (see the note in the code above)

但是，我知道这样做可能会有更好的方法-如果是这样，其他建议将不胜感激！

But, I know that there might be a much better way to do this -- if so, other suggestions would be much appreciated!

推荐答案

假定数据从第2行开始.请确保范围D1不为空.

Assumes data starts in row 2. Make sure Range D1 is not empty.

Sub CopyRows2()

Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet

'clear enough columns for ~30 data sets
Columns("E:CA").ClearContents

Set ws = Worksheets("Sheet2")
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
x = 2

With ws

    'fill columns A+B to correspond with C
    Do Until Cells(lastRow, 1) <> ""

        If .Cells(x, 2) <> .Cells(x, 3) Then
            .Range("A" & x + 1 & ":B" & x + 1).Insert Shift:=xlDown
            .Cells(x + 1, 1) = .Cells(x, 1)
            .Cells(x + 1, 2) = .Cells(x, 2)
        End If

        x = x + 1

    Loop

    'move blocks
    i = 2
    c = 1


    Do Until i > lastRow

        'change in column A
        If .Cells(i + 1, 1) <> .Cells(i, 1) Then
            .Range("c" & i - c + 1 & ":D" & i).Copy
            lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            .Cells(1, lastCol + 1).PasteSpecial
            c = 0
        End If

        i = i + 1
        c = c + 1

    Loop

End With

End Sub

这篇关于在另一个数组的基础上选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！