VBA read Column into array and based on IF conditi

问题:

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

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

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.

I have to loop through 100 000 rows

回答1:

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

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.

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

回答2:

"F1" is formula cell. I believe you can correct.

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:D" & 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) = "Male" Then
                y(i, 1) = x(i, 3) + x(i, 4)
            Else
                y(i, 1) = False
            End If
        Next i
        Range("b1").Resize(UBound(y), 1).Value = y '<~~ "b1" your formula exist cell
End Sub