VBA将列读入数组并基于两列的IF条件总和值 [英] VBA read Column into array and based on IF condition sum values from two columns

问题描述

正如@sktneer在我以前的查询中正确建议的那样，当处理大数据时，将数据读入数组.

As @sktneer rightly suggested in my previous query to read data into an Array when dealing with large data.

我想通读范围A的数组到最后，如果A1中的值等于"L"，则添加B1 + C1

I want to Read through an array of Range A to the end and if the value in A1 is equal to "L" then add B1 + C1

我正在将此公式转换为以下代码=IF(A1="Male",C1+D1,FALSE)

I am converting this formula to below code =IF(A1="Male",C1+D1,FALSE)

Sub ANewMacro()
    Dim lr As Long, i As Long, j As Long
    Dim c, d, x, y()
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    x = Range("A1:A" & lr).Value
    c = Range("C1:C" & lr).Value
    d = Range("D1:D" & lr).Value
        ReDim y(1 To UBound(x, 1), 1 To 1)
        j = 1
        For i = 1 To UBound(x, 1)
            If x(i, 1) = "L" Then
                y(i, 1) = c(i, 1) + d(i, 1)
                j = j + 1
            ElseIf x(i, 1) = "S" Then
                y(i, 1) = c(i, 1) + d(i, 1)
                j = j + 1
            Else
                y(i, 1) = "NULL"
                j = j + 1
            End If
        Next i
    Range("B1").Resize(UBound(y), 1).Value = y
End Sub

代码按预期工作，但想知道声明多个范围的方法是否正确以及执行情况.

The code works as desired but wanted to know if the method of declaring multiple range is correct and also the execution.

我必须遍历10万行

推荐答案

您可以将整个数据读取到单个数组中，该数组称为多维数组.

You can read the whole data into a single Array which will be called multidimensional array.

根据您现有的代码，您可以尝试类似的操作...

As per your existing code, you may try something like this...

Sub ANewMacro()
    Dim lr As Long, i As Long, j As Long
    Dim x, y()
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    x = Range("A1:D" & lr).Value
    ReDim y(1 To UBound(x, 1), 1 To 1)
    j = 1
    For i = 1 To UBound(x, 1)
        If x(i, 1) = "L" Then
            y(i, 1) = x(i, 3) + x(i, 4)
        ElseIf x(i, 1) = "S" Then
            y(i, 1) = x(i, 3) + x(i, 4)
        Else
            y(i, 1) = "NULL"
        End If
        j = j + 1
    Next i
    Range("B1").Resize(UBound(y), 1).Value = y
End Sub

在上面的代码中x(i，1)代表A列中的数据，x(i，3)代表C列中的数据，x(i，4)代表D列中的数据.

In the above code x(i, 1) represents data in column A, x(i, 3) represents data in column C and x(i, 4) represents the data in column D.

现在，因为如果列A为"L"或"S"，则您执行的是相同的计算，因此您可以像下面那样替换For循环...

Now since if the Column A is either "L" or "S" you are performing the same calculation so you may replace the For Loop like below...

For i = 1 To UBound(x, 1)
    If x(i, 1) = "L" Or x(i, 1) = "S" Then
        y(i, 1) = x(i, 3) + x(i, 4)
    Else
        y(i, 1) = "NULL"
    End If
    j = j + 1
Next i

这篇关于VBA将列读入数组并基于两列的IF条件总和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！