CODEWITHSUNDEEP
vbaexcel
alex1stef2
alex1stef2

Reputation: 105

Variable value being forgotten after loop VBA

I am currently trying to set up a Do While loop in which one part of the loop only executes once. I am effectively trying to define a cell range for one sheet, and then have my loop apply this same cell range to all sheets/workbooks without having to re-specify the range.

Here is what I have so far:

isExecuted = False
Do While FileName <> ""
    ' Open a workbook in the folder
    Set WorkBk = Workbooks.Open(Folder & "\" & FileName)

        ' Conditional to select range only once
        If Not isExecuted Then

            Dim rng As Range
            Set rng = Application.InputBox("Select a Range", "Obtain Range Object", Type:=8)
            Debug.Print (rng.Address)
            MsgBox "The cells selected were " & rng.Address
            isExecuted = True

        End If


    Set SourceRange = WorkBk.Worksheets(1).Range(rng.Address)

' more stuff goes here

In debug mode. the first time this loop executes everything works as expected, and I can see that my rng.Address is the cell range specified. However, on the second loop rng.Address becomes <ObjectRequired>, and so the rest of the script fails. Any ideas as to how to permanently set rng.Address to the specified cell range?

Upvotes: 2

Views: 1794

Answers (3)

Moosli
Moosli

Reputation: 3285

The Problem is, that the Range Object is connected to the First Workbook. So when you close that workbook, the Range Object will be lost.

You need to Save the Address in an String Variable, so you can access it on every Loop.

    Dim strAddress as String
    isExecuted = False
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(Folder & "\" & FileName)

            ' Conditional to select range only once
            If Not isExecuted Then

                Dim rng As Range
                Set rng = Application.InputBox("Select a Range", "Obtain Range Object", Type:=8)
                Debug.Print (rng.Address)
                MsgBox "The cells selected were " & rng.Address
                strAddress = rng.Address
                isExecuted = True

            End If


        Set SourceRange = WorkBk.Worksheets(1).Range(strAddress )

    ' more stuff goes here

Upvotes: 4

David Zemens
David Zemens

Reputation: 53623

Hard to say what the specific problem is without seeing more code, but I would suggest a more concise logic, like instead of this:

    If Not isExecuted Then

        Dim rng As Range
        Set rng = Application.InputBox("Select a Range", "Obtain Range Object", Type:=8)
        Debug.Print (rng.Address)
        MsgBox "The cells selected were " & rng.Address
        isExecuted = True

    End If

Do this:

If rng is Nothing Then
    Set rng = Application.InputBox("Select a Range", "Obtain Range Object", Type:=8)
End If

NB: If you're closing the workbook during the ' more stuff goes here part, that probably is killing the object reference to the range

Any ideas as to how to permanently set rng.Address to the specified cell range?

Yes, just preserve the Address rather than the object. Since the Address is a string literal, you can retain this in a String variable even if the object reference goes out of scope:

Dim addr As String
If addr = vbNullString
    addr = Application.InputBox("Select a Range", "Obtain Range Object", Type:=8).Address
End If
Set SourceRange = WorkBk.Worksheets(1).Range(addr)

Upvotes: 7

phaen
phaen

Reputation: 388

You need to declare the variable outside the if statement, better outside the loop. Else you cant access it outside the if/loop statement.

Upvotes: -3

Related Questions