How to transpose scripting dictionary keys and retain "05" and "5" as two distinct values?

Question

I created a table to include/exclude distinct values from a larger dataset. This table is made with a scripting dictionary.

The dataset could contain numeric values as a parameter which should be considered as a textual value.
Example: "05" and "5" should be considered as two distinct values.

The scripting dictionary saves the two values as two distinct values but when I use the Application.Transpose code for the keys the keys are considered as one value ("5").

Is there a way to paste the keys as two distinct values?
I've tried to add a single quote (') in front of the relevant data but this didn't appear in the keys.

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

score 1 · Answer 1 · answered Jul 02 '21 at 10:02

If your Targetrange has the number format General it will consider the string 05 as a number and convert it to 5.

So either change your number format to text before you add the values:

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

or add a ' in the dictionary before every entry:

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

Off topic note:

the Range() method can take cells and does not need addresses so you can remove .Address() from

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

and just write

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

or even shorter:

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

if it does not begin in row 1 but row n then it is

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

@Fabian_W for up-voting you need reputation, but accepting it should work without reputation after 1 hour at least. — Pᴇʜ, Jul 02 '21 at 11:08

score 1 · Answer 2 · answered Jul 02 '21 at 10:34

1

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

answered Jul 02 '21 at 10:34

CDP1802

13,871
2
7
17

1

`.Column` is of type `Long` therefore `c` must be of the same type. – Pᴇʜ Jul 02 '21 at 10:51
@Pᴇʜ Why ? `Range("XFD1").Column` is 16,384 , integer limit is 32,767 – CDP1802 Jul 02 '21 at 12:06
Because `Debug.Print TypeName(Range("XFD1").Column)` says it is `Long`. And if you don't use the same type you have implicit type conversions all over your code. Actually I recommend **always** to use `Long` instead of `Integer` as there is no benefit in using `Integer`. You might want to read why [here](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520). – Pᴇʜ Jul 02 '21 at 12:10
The first `' change` is inaccurate. You could fix it with something like `Dim r as long: r = aCell.Row` ... `ar = .Cells(r, c).Resize(lr - r + 1).Value2`. Also, I prefer `.Rows.Count`. – VBasic2008 Jul 02 '21 at 16:43
@VBasic2008 `c = Range(Cells(1, aCell.Column), etc` so is not dependent on `aCell.row`. – CDP1802 Jul 02 '21 at 17:10
Your focus should be on `.Cells(1, c)`. If e.g. the user chooses the cell `A3`, then the array will, after the resize, also contain the values from `A1:A2`. – VBasic2008 Jul 02 '21 at 18:43
@VBasic2008 Yes, but that is how the posted code is written and I am not changing it. – CDP1802 Jul 02 '21 at 18:57
You don't have to, but it's working correctly only for the first row so you should at least remove the comment or change it to (`' change column`). – VBasic2008 Jul 02 '21 at 19:04
@VBasic I think you misunderstood. I am not changing the way in which the code snippet posted in the question works, which is to start at row 1 regardless of what `aCell.row` is. – CDP1802 Jul 02 '21 at 19:29

How to transpose scripting dictionary keys and retain "05" and "5" as two distinct values?

2 Answers2