Set Destination to last row in worksheet - Object Required error

Question

I am calling this procedure three times as I wish to append a series of CUSIPs to a VBA Scripting dictionary.

Sub Import_RB_Dict(ws_string As String)
'Uses Scripting Dictionary instead of loop to sum up the TNA of funds in the CNR
'Much faster than traditional loop

Sheets(ws_string).Activate



LR = Range("a1000").End(xlUp).Row
LC = Range("zz1").End(xlToLeft).Column

ReDim Source(1 To LR, 1 To LC)
Source = Range(Cells(1, 1), Cells(LR, LC))
ReDim aRB_Dict(1 To LR, 1 To 2000)


Set RB_List = CreateObject("scripting.dictionary")

Row = 0
For r = 2 To LR
    If RB_List.Exists(Source(r, 3)) Then
        Array_Row = RB_List(Source(r, 3))
        'aRB_Dict(Array_Row, 2) = aRB_Dict(Array_Row, 2) + Source(r, 65) / Source(r, 47)
    Else
        Row = Row + 1
        With RB_List
            .CompareMode = vbTextCompare
            .Add Source(r, 3), Row
        End With
        aRB_Dict(Row, 1) = Trim(CStr(Source(r, 3)))
        'aRB_Dict(Row, 2) = Source(r, 65) / Source(r, 47)
    End If
Next r

Sheets("Request Builder").Select
LastRow = Range("a6500").End(xlUp).Row
Set Destination = LastRow
Destination.Resize(UBound(aRB_Dict, 1) + 1, UBound(aRB_Dict, 2)).Value = aRB_Dict

End Sub

And I am calling this procedure through the below

Set RB_List = CreateObject("scripting.dictionary")
RB_List.RemoveAll
Set RB_List = Nothing
Set RB_List = CreateObject("scripting.dictionary")

Import_RB_Dict "Main"
Import_RB_Dict "ETF"
Import_RB_Dict "MAV"

But my main code will not run as I am not able to assign Set Destination = LastRow? I receive the error Object Required in fact the procedure will not even begin to run. By use of the LastRow variable I would like to identify the last blank cell in column A and append all the CUSIPs within the dictionary to that cell. My goal is to use to dictionary to scan over each sheet and compile a unique list of all unique securities among each of the three worksheets and then append them to the Request Builder Sheet.

Surely you can just reference the range directly: LastRow.Resize(UBound... etc alternatively dim LastRow as Integer and dim Destination as Range then set Destination = Range("A" & LastRow) — Glitch_Doctor, Jun 15 '16 at 15:35

score 1 · Answer 1 · answered Jun 15 '16 at 15:33

1

The Set-keyword is used for declaring objects, when you want to declare a variable, just use Dim. You should declare lastRow and destination as integers. Then destination = lastRow wont throw any errors. (Although vba interprets every undeclared variable with the Variant-type, so technically you can just delete Set).

answered Jun 15 '16 at 15:33

Tom K.

1,020
1
12
28

1

To complement this answer, the line LastRow = Range("a6500").End(xlUp).Row, should be LastRow= Cells(Rows.Count, "A").End(xlUp).Row instead – Sgdva Jun 15 '16 at 15:37
An `Invalid Qualifier` error throws an exception on `destination.Resize(UBound(aRB_Dict, 1) + 1, UBound(aRB_Dict, 2)).Value = aRB_Dict` with this answer – phillipsK Jun 15 '16 at 16:00
I'm not really sure what you are trying to accomplish with that that line. Generally speaking all `.Methods` only work for objects. As explained before, you are declaring a variable containing an `integer`. When you want to resize your array, just use `ReDim` again. – Tom K. Jun 16 '16 at 14:12
Also see: http://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba – Tom K. Jun 16 '16 at 14:12

Set Destination to last row in worksheet - Object Required error

1 Answers1