set range? RRS feed

  • Question

  • I set the range as follows.

    when I try to execute from other sheets other than "SSS" sheet 

    it returns error.

    I want to use cell number to change it later.

    Dim rng As Range

    Set rng = Worksheets("SSS").Range(Cells(1, 16), Cells(300, 16))

    please help me.

    thank you

    • Edited by jjuunn Wednesday, May 16, 2018 11:54 AM
    Wednesday, May 16, 2018 11:52 AM

All replies

  • You need to refer to the worksheet when you use Cells too:

    Set rng = Worksheets("SSS").Range(Worksheets("SSS").Cells(1, 16), Worksheets("SSS").Cells(300, 16))

    Otherwise, Cells would refer to the active worksheet. You can shorten it by using With … End With:

    With Worksheets("SSS")
        Set rng = .Range(.Cells(1, 16), .Cells(300, 16))
    End With

    Note the . in .Range and .Cells - it indicate that they refer to the object specified in With Worksheets("SSS").

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 16, 2018 12:20 PM