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. This one is relevant: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset) — user6432984
– user6432984, Commented Aug 3, 2017 at 4:23
FYI - Don't use .Select. 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
– BruceWayne, Commented Aug 3, 2017 at 4: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
– itsMe, Commented Aug 3, 2017 at 5: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
– BruceWayne, Commented Aug 3, 2017 at 5:15

Subodh Tiwari sktneer · Accepted Answer · 2017-08-11 11:09:01Z

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, 2017 at 11:09

answered Aug 3, 2017 at 5:16

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

itsMe Over a year ago

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

Subodh Tiwari sktneer Over a year ago

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... powerspreadsheets.com/excel-vba-array

itsMe Over a year ago

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?

Subodh Tiwari sktneer Over a year ago

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.

itsMe Over a year ago

Sure @sktneer I have opened a new question for this query. Would appreciate your opinion on it. stackoverflow.com/q/45482738/2209761

|

Tony M · Accepted Answer · 2017-08-03 05:02:21Z

1

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.

answered Aug 3, 2017 at 5:02

Tony M

1,7583 gold badges21 silver badges38 bronze badges

1 Comment

itsMe Over a year ago

Thanks Tony, this really helps. I see with your version of the code, you have saved me two lines.

Collectives™ on Stack Overflow

VBA Macro Loop Display Value in specific column

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related