CODEWITHSUNDEEP
excelvba
wildcat89
wildcat89

Reputation: 1285

Object variable or With block variable not set. Why?

I'm getting the error mentioned here:

Error

…From this section of code...

Code1

...and as you'll see, the cell variables for the lastrow and lastcol are reporting fine, so why am I getting this error?

code2

code3

I tried adjust it as so too, but still the same issue.

code4

Basically, I'm just trying to set Rg as a range using cell values as determined by the two variables above. Using Excel 2016 VBA. Thanks. Will check back tomorrow.

Upvotes: 0

Views: 383

Answers (2)

Error 1004
Error 1004

Reputation: 8230

Please find below how to set ranges:

Sub test()

    Dim rng_Single As Range, rng_Union As Range

    'Refer to the sheet you want to work with
    With ThisWorkbook.Worksheets("Sheet1")

        'Use the "." before range or cell in order to refer to the worksheet mention in the with statement

        'Set the range.
        Set rng_Single = .Range(.Cells(1, 1), .Cells(1, 5))
        'Merge two ranges of the same worksheet
        Set rng_Union = Union(.Range(.Cells(3, 1), .Cells(3, 5)), .Range(.Cells(4, 1), .Cells(4, 5)))

    End With

End Sub

Upvotes: 1

ACCtionMan
ACCtionMan

Reputation: 511

Range is an Object so you need to use the Set command rather than just '=' which you can use for standard variables such as Sting, Integer etc. So in your example:

Set Rg = Range(Cells(1, 1), Cells(lastRow, LastCol))

Upvotes: 2

Related Questions