vba error: object variable or with block variable not set

4
votes

I'm getting a "object variable or with block variable not set" error message when running the following code. It is in Access and refers to an Excel spreadsheet. What is wrong with the code?

wsTest.Range("A11").Activate    

Do Until IsEmpty(ActiveCell)
    intX = intX + wsTest.Cells(ActiveCell.Row, "L") 'error occurs on this line
    intY = intY + wsTest.Cells(ActiveCell.Row, "I")
    ActiveCell.Offset(1, 0).Select ' Step down 1 row to the next cell.
Loop

The first time the code is run, there is no error, only the second time. Closing and reopening Access "fixes" the problem. How can that be related to this code?

3
ms-accessvbaexcel
On what line does the error occur? – mwolfe02
I updated the code above with that info. – LFurness
ActiveCell refers to an Excel object, so you likely should qualify it with your XL application reference. Eg: xlApp.Activecell – Tim Williams

3 Answers

2
votes

You need to define the sheet, like so:

Dim wsTest As Worksheet

Set wsTest = Sheets("Sheet1")
1
votes

As the error occurs in the highlighted line, I believe you're trying to do an invalid sum. Maybe the value returned by wsTest.Cells(oCell.Row, "L") isn't a integer, or you should use wsTest.Cells(oCell.Row, "L").Value instead.

Will be easier to identify the root cause of the problem adding a msgbox to check the cell value, as I added below.

Either way, I'd suggest you to avoid using references to ActiveCell, as they aren't reliable. Maybe this code helps avoiding this error (simply replacing the ActiveCell by a proper cell object).

Dim oCell as excel.range

set oCell = wsTest.Range("A11")

Do Until Len(oCell.Value) = 0
    msgbox(wsTest.Cells(oCell.Row, "L"))
    intX = intX + wsTest.Cells(oCell.Row, "L") 'error occurs on this line
    intY = intY + wsTest.Cells(oCell.Row, "I")
    Set oCell = oCell.Offset(1, 0) ' Step down 1 row to the next cell.
Loop
0
votes

Try changing 

wsTest.Range("A11").Activate

to 

wsTest.Range("A11").Select