☰

宏用于检查并将唯一值放入列范围内的单元格中

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2013
宏用于检查并将唯一值放入列范围内的单元格中

我对宏还很陌生。

我想将以下公式转换为宏

=IF(COUNTIF($B$1:$B1,$A2)=1,"NA",$A2)

其中 $B1 将是增量，我将打印 $B2 中的值。

最终输出应如下所示

在此处输入图片描述

答案1

是不是你只是不想有重复？使用脚本字典。

Dim dict as Object
set dict = CreateObject("Scripting.Dictionary")
Dim i as Long
For i = 2 to 9
   dict(cells(i,1)) = 1
Next
Dim dictKey as Variant
i = 1
For Each dictKey in dict.Keys()
   cells(i,2) = dictKey
   i = i+1
Next

或者在最后：

dim dictVal as string
For i = 2 to 9
  dictVal = cells(i,1)
  If Dict(dictVal).Exists Then
    cells(i,2) = cells(i,1)
  Else: cells(i,2) = "N/A"
  End if
Next

或者如果你想要粗暴的方式

Sub testing()
    Dim i As Long
    Dim j As Long
    Dim c As Range
    For i = 2 To 9
        With Range(Cells(i + 1, 1), Cells(9, 1))
            Set c = .Find(Cells(i, 1), LookIn:=xlValues)
                If c Is Nothing Then
                    Cells(i, 2) = Cells(i, 1)
                Else: Cells(i, 2) = "N/A"
                End If
        End With
    Next
End Sub

相关内容