我正在尝试从一个工作表中搜索一列单元格,找到所有唯一值,然后将这些值粘贴到另一个工作表中的列中。到目前为止,我拥有创建字典,搜索所需列并选择该列中所有唯一值的代码。
Function UniqueRequest() As Long
myReqIDCol = ColSearch("id")
'Creates a dictionary filled with each unique value in the "TaskIDList" column and counts them to determine how many unique keys are in the document
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To LastRow
tmp = Cells(i, myReqIDCol).Value
If Not dic.exists(tmp) Then
dic.Add tmp, 1
End If
Next i
End Function
我还有一个功能,用于选择要粘贴单元格的工作表并进行设置,以便将值粘贴到所需列中的每个连续空白单元格中。
Function ReqSheet(input_column As Integer, input_value As Long) As Long
Dim rv As Long
rv = 1
Sheets("Request Results").Activate
Do While Cells(rv, input_column).Value <> ""
rv = rv + 1
Loop
Cells(rv, input_column).Value = input_value
ReqSheet = input_value
End Function
我的问题是我不确定如何将这两者联系起来。我想用字典的每个值调用ReqSheet函数,但是我尝试过的一切都失败了。抱歉,这是一个简单的解决方法,但是我真的不能从互联网上找到好的解决方案,而且我对VBA还是很陌生。
使用此代码并将列更改为您要使用的任何内容。
Function UniqueRequest() As Long
myReqIDCol = ColSearch("id")
'Creates a dictionary filled with each unique value in the "TaskIDList" column and counts them to determine how many unique keys are in the document
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To LastRow
tmp = Cells(i, myReqIDCol).Value
If Not dic.exists(tmp) Then
dic.Add tmp, 1
End If
Next i
For Each value in dic.keys
ReqSheet(4,value) 'I have taken column 4,you can change it to any no you want.
End Function
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句