VBA Macro Loop Display Value in specific column

Question

I am new to VBA and I want to replicate below code in order to automate the process

=IF(A2="Male","M","F")

I have coded it as below to show the value in the 5th column ('F') offset of 'A'

Sub Gender1()
'
' Gender1 Macro
'
' =IF(A2="Male","M","F")

    Dim rCell As Range
    Dim rRng As Range

    Set rRng = Range("A2", Range("A2").End(xlDown))

    For Each rCell In rRng.Cells
        If rCell.Value = "Male" Then
            result = "M"
            rCell.Offset(0, 5).Select
            ActiveCell.Value = result
        ElseIf rCell.Value = "Female" Then
            result = "F"
            rCell.Offset(0, 5).Select
            ActiveCell.Value = result
        Else
            result = "NULL"
            rCell.Offset(0, 5).Select
            ActiveCell.Value = result
        End If

    Next rCell

End Sub

I wanted to understand if the method is correct and should offset be used to display value or is there a better way to optimize the code and display value in the desired column.

I have to loop through 100 000 rows

YOu should watch this series on Youtube: [Excel VBA Introduction](https://www.youtube.com/watch?v=KHO5NIcZAc4&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5). This one is relevant: [Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)](https://www.youtube.com/watch?v=c8reU-H1PKQ&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&index=5) — , Aug 03 '17 at 04:23
FYI - [Don't use `.Select`](https://stackoverflow.com/questions/10714251). At the very least it saves you an extra line. Also, I assume the offset column 5 is empty? You could alternatively just paste your formula to the entire range, saving you from looping through the cells. — BruceWayne, Aug 03 '17 at 04:43
@BruceWayne Thank you for pointing this out. Removing '.Select' also helps in executing the code faster when it has to loop through 1 Lac rows. — itsMe, Aug 03 '17 at 05:11
So in column F, you have that formula? And you jus want to bypass running the formula manually u on X many rows? — BruceWayne, Aug 03 '17 at 05:15

Subodh Tiwari sktneer · Accepted Answer · 2017-08-11T11:09:01.830

2

If you are dealing with 1 lac rows, I would suggest you to adopt another approach.

Read the column A data into an Array and construct another Array based on your IF condition and write the resultant array at once onto the Sheet.

Since looping over 100 thousand rows will take time if you interact with the cells but the Array approach will take maybe less than a second.

Give this a try to see the difference...

The below code took less than a second when I ran this code on 150000 rows of data.

Sub Gender()
Dim lr As Long, i As Long
Dim x, y()
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("A2:A" & lr).Value
ReDim y(1 To UBound(x, 1), 1 To 1)
For i = 1 To UBound(x, 1)
    If x(i, 1) = "Male" Then
        y(i, 1) = "M"
    ElseIf x(i, 1) = "Female" Then
        y(i, 1) = "F"
    Else
        y(i, 1) = "NULL"
    End If
Next i
Range("F2").Resize(UBound(y), 1).Value = y
End Sub

edited Aug 11 '17 at 11:09

answered Aug 03 '17 at 05:16

Subodh Tiwari sktneer

9,906
2
18
22

1

This works like a charm! I ran this code on 150000 rows and it actually executed in less than a second. Thank you very much @sktneer. I will study your code and get better understanding about ReDim, UBound and Resize – itsMe Aug 03 '17 at 06:14
1

I explained the reason for using Arrays in my solution i.e. while you work with arrays, once you get your data into an array you don't interact with excel objects like cells/ranges etc anymore and do all the required calculations with the arrays using system memory and in the end you write the result onto the sheet. You need to explore the Array concept specially if you are dealing with large data sets and you have witnessed the performance of arrays in your example. For a starting point, you may find this link useful... https://powerspreadsheets.com/excel-vba-array/ – Subodh Tiwari sktneer Aug 03 '17 at 07:07
If I want to tweak this to 'IF Male' Then 'Add Column C + D', Will it be right to declare C & D range like 'c = Range("C1:C" & lr).Value' and execute like this 'y(i, 1) = c(i, 1) + d(i, 1)'. @sktneer Could you please help understand what does j = j + 1 do? – itsMe Aug 03 '17 at 10:30
I think you should open a New Question and post the code in the description and share the screenshot of your data and also the screenshot of your desired output. – Subodh Tiwari sktneer Aug 03 '17 at 11:00
Sure @sktneer I have opened a new question for this query. Would appreciate your opinion on it. https://stackoverflow.com/q/45482738/2209761 – itsMe Aug 03 '17 at 11:23
could you help understand the role of 'j' here as it is not being used? – itsMe Aug 11 '17 at 09:06
@itsMe Good catch. :) I removed the unused variable from the code. – Subodh Tiwari sktneer Aug 11 '17 at 11:09

score 1 · Answer 2 · answered Aug 03 '17 at 05:02

To mimic the original function =IF(A2="Male","M","F") the following code works:

Sub mimicIf()
Dim r As Range
Dim cell As Range
  Set r = Range("A2")
  Set r = Range(r, r.End(xlDown))
  For Each cell In r
    If cell = "Male" Then
      cell.Offset(0, 5) = "M"
    Else
      cell.Offset(0, 5) = "F"
    End If
  Next cell
End Sub

However, your code is actually for the function =IF(A2="Male","M",IF(A2="Female","F","Null"))

As BruceWayne commented, you should avoid .Select and ActiveCell, unless really needed. As far as using .offset(), it's quite efficient, assuming you are using ranges. If you really need efficiency, however, doing manipulations in code using arrays/variants is often the answer.

Thanks Tony, this really helps. I see with your version of the code, you have saved me two lines. — itsMe, Aug 03 '17 at 05:08

VBA Macro Loop Display Value in specific column

2 Answers2

Linked