VBA宏循环显示特定列中的值 [英] VBA Macro Loop Display Value in specific column

问题描述

我是VBA新手，我想复制下面的代码，以便自动化过程

  = IF（A2 = 男，M，F）
  

我编码如下， A的第五列（'F'）偏移量的值

  Sub Gender1（）
'
'Gender1 Macro 
'
'= IF（A2 =Male，M，F）
 
 Dim rCell As Range 
 Dim rRng作为范围
 
设置rRng =范围（A2，范围（A2）。结束（xlDown））
 
每个rCell在rRng.Cells 
然后
 result =M
 rCell.Offset（0，5）。选择
 ActiveCell.Value = result 
 ElseIf rCell.Value = 女然后
 result =F
 rCell.Offset（0，5）。选择
 ActiveCell.Value = result 
 Else 
 result =NULL 
 rCell.Offset（0，5）。选择
 ActiveCell.Value = result 
结束如果
 
下一个rCell 
 
 End Sub 
  

我想了解该方法是否正确，应该使用偏移量来显示值，还是有更好的方法来优化代码并显示所需列中的值。

我必须循环执行100 000行

解决方案

如果你正在处理1行，我建议您可以采用另一种方法。

将列A数据读入数组，并根据您的IF条件构建另一个数组，并将生成的数组立即写入Sheet。 / p>

由于循环十万行将需要时间，如果您与单元格进行交互，但Array方法将需要不到一秒钟。

尝试看到差异...

当我在150000行数据上运行这段代码时，下面的代码花了不到一秒钟。

  Sub性别（）
 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）
如果x（i，1） =男然后
y（i，1）=M
 ElseIf x（i，1）=女然后
y（i，1）=F
 Else 
y（i，1）=NULL
 End If 
 Next i 
范围（F2）。调整大小（UBound（y），1）.Value = y 
 End Sub 
  

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

解决方案

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

这篇关于VBA宏循环显示特定列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！