时间戳

首页 > 解决方案 > 如何根据另一列单元格中的条目更新列中的单元格？

excel - 如何根据另一列单元格中的条目更新列中的单元格？

问题描述

如果 C 列包含“100”，则 D 列应更新为“001”。

如果 C 列包含“101”，则 D 列应更新为“002”。

这是 C 列中的单元格区域所必需的。

我尝试了下面的代码，但它不起作用。

Sub iflocation()
If activecell.Range("c1:C").Value = 100 Then Range("d1"d").Value = Range "001.Value
If activecell.Range("c1:C").Value = 101 Then Range("d1"d").Value = Range "002.Value
End sub

标签： excelvba

解决方案

内存的最快方法之一是将整列添加到数组中。遍历列并保存行索引，这在集合中满足您的条件。

Sub MainSub()
Dim vArr As Variant
Dim i As Long

'collection contains row indexes, where the value in column C = 100
Dim col100 As New Collection
'collection contains row indexes, where the value in column C = 101
Dim col101 As New Collection
Dim wsh As Worksheet
Dim itm As Variant

Set wsh = Sheets("Sheet1")
vArr = wsh.Columns("C:C")

'iterate through the array and collect corresponding indexes
For i = LBound(vArr) To UBound(vArr)
    If CStr(vArr(i, 1)) = "100" Then
        col100.Add i
    ElseIf CStr(vArr(i, 1)) = "101" Then
        col101.Add i
    End If
Next i

'call function which sets predefined value into column D
SetNewValueInColumnD wsh, col100, "001"
SetNewValueInColumnD wsh, col101, "002"
End Sub

Private Sub SetNewValueInColumnD(ByRef wsh As Worksheet, ByRef p_col As Collection, 
ByRef p_value As String)
Dim itm As Variant
For Each itm In p_col
    With wsh.Range("D" & itm)
        .NumberFormat = "@"
        .Value2 = p_value
    End With
Next itm

推荐阅读