Range is set to nothing but cell references within range are valid

Question

I have some VBA in an Excel 2010 document which returns "nothing" when I try to assign a range from a different worksheet. I can reference the value of a cell within the range with no problems.

Why does the code below leave the rng variable set to Nothing, when the value of s3 correctly contains the value of the first cell in that range??

Dim s3 As String
    Dim rng As Range

    Set SourceFile = Application.Workbooks.Open("c:\finance\inv.xls")
    Set SourceSheet = SourceFile.Worksheets(Source)

    s3 = SourceSheet.Range("A3").Value
    rng = SourceSheet.Range("A3:A30")

Barry Barry · Accepted Answer · 2014-10-20T17:54:41

Use

Set rng = SourceSheet.Range("A3:A30")

I do not understand why you do not get the error message

Object variable or With block variable not set

Are you using On Error Resume Next to suppress errors?

Range is set to nothing but cell references within range are valid

1 Answers