VBA read Column into array and based on IF condition sum values from two columns

Question

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

Looks ok. You could declare y as a 1D array, and combine the first two clauses of your If statement, but these are minor points. Also, you don't need d. — SJR, Aug 03 '17 at 11:45
Working code should be posted on [Code Review](https://codereview.stackexchange.com) not StackOverflow. There is nothing wrong with how you are handling your arrays. Because j = j + 1 is present at the end of each case of your if statement, it should be moved outside of your if statement. But then again j will always be equal to i -1 and j is actually not used. — , Aug 03 '17 at 12:29
=IF(A1="Male",B1+C1,FALSE) the formula is which column's cell? — Dy.Lee, Aug 03 '17 at 12:43
Sorry, there seems to be a mistake. Formula is in B Column, the correct formula should be =IF(A1="Male",C1+D1,FALSE). I have updated the formula — itsMe, Aug 03 '17 at 12:50

score 1 · Accepted Answer · answered Aug 03 '17 at 13:26

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

Dy.Lee · Answer 2 · 2017-08-03T13:34:52.637

0

"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

edited Aug 03 '17 at 13:34

answered Aug 03 '17 at 12:49

Dy.Lee

7,527
1
12
14

Thanks Dy.Lee, this looks good and Range "A1:D" helps save me 2 lines too. But my formula is B cell i.e. Looping through A and if condition met then applying C+D in B. What would the best way be to optimize this in one line? – itsMe Aug 03 '17 at 12:55
1

@itsMe: modified my answer. – Dy.Lee Aug 03 '17 at 13:35

VBA read Column into array and based on IF condition sum values from two columns

2 Answers2

Linked