如何转置脚本字典键并将"05"和"5"保留为两个不同的值？

Question

我创建了一个表来包含/排除较大数据集中的不同值。这个表是用脚本字典制作的。

数据集可以包含数值作为参数，该参数应被视为文本值
示例："05〃；以及"；5〃；应被视为两个不同的值。

脚本字典将这两个值保存为两个不同的值，但当我使用键的Application.Transpose代码时，键被视为一个值("5"(。

有没有办法将键粘贴为两个不同的值
我曾尝试在相关数据前面添加一个引号('(，但没有出现在键中。

If Not aCell Is Nothing Then
Dim d As Object, c As Variant, j As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
Worksheets("DATASET").Activate
lr = Cells(Rows.Count, aCell.Column).End(xlUp).Row
c = Range(Cells(1, aCell.Column).Address(), Cells(lr, aCell.Column).Address())
For j = 1 To UBound(c, 1)
d(c(j, 1)) = 1
Next j
Targetrange.Resize(d.Count) = Application.Transpose(d.keys)
End If

Answer 1

如果您的Targetrange具有数字格式General，则会将字符串05视为数字，并将其转换为5。

因此，在添加值之前，请将数字格式更改为文本：

Targetrange.NumberFormat = "@"
Targetrange.Resize(d.Count) = Application.Transpose(d.keys)

或者在字典中的每个条目之前添加一个'：

d("'" & c(j, 1)) = 1

---

主题外注释：

Range()方法可以获取单元格，不需要地址，因此可以从中删除.Address()

Range(Cells(1, aCell.Column).Address(), Cells(lr, aCell.Column).Address())

只写

Range(Cells(1, aCell.Column), Cells(lr, aCell.Column))

甚至更短：

Cells(1, aCell.Column).Resize(RowSize:=lr)

如果它不是从1行开始而是从n行开始，则它是

Cells(n, aCell.Column).Resize(RowSize:=lr - (n - 1))

Answer 2

Option Explicit
Sub macro1()
Dim ws As Worksheet, aCell As Range, TargetRange As Range
Dim ar As Variant, lr As Long, j As Long, c As Integer

Dim d As Object, key As String
Set d = CreateObject("Scripting.Dictionary")
Set ws = Worksheets("DATASET")
With ws
Set aCell = .Range("A1") ' change
c = aCell.Column
lr = .Cells(Rows.Count, c).End(xlUp).Row
ar = .Cells(1, c).Resize(lr).Value2
End With
For j = 1 To UBound(ar)
key = Trim(ar(j, 1))
If Len(key) > 0 Then
d(key) = 1
End If
Next
Set TargetRange = Sheet2.Range("A1") ' change
With TargetRange.Resize(d.Count)
.NumberFormat = "@" ' text
.Value2 = Application.Transpose(d.keys)
End With
MsgBox d.Count & " keys in d"
End Sub