"Object Variable or With Block Variable not Set"

Question

I am trying to loop through the workbook, take some value and store in a different sheet. Below is my code. Keepp getting VBA Object Variable or With Block Variable not Set Error.

Can someone help me spot where is wrong?

Sub analysis()

Dim i As Integer
Dim ws As Worksheet

For i = 4 To 197
    ws = ActiveWorkbook.Sheets(i)
    ws.Cells(1, 9) = ìstandardevî
    ws.Cells(2, 9) = Application.WorksheetFunction.StDev(Range("D3:D34"))
    ActiveWorkbook.Sheets(4).Cells(2, 1) = ws.Cells(2, 1)
    Sheets(4).Cells(2, 2) = ws.Cells(3, 8)
    Sheets(4).Cells(2, 3) = ws.Cells(2, 9)
Next i


End Sub

Excel Hero · Accepted Answer · 2015-11-03 02:49:12Z

4

The first line inside the For/Next loop is this:

ws = ActiveWorkbook.Sheets(i)

Change it to this:

Set ws = ActiveWorkbook.Sheets(i)

answered Nov 3, 2015 at 2:49

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JamesFaix Over a year ago

In VBA, any variable that is not a primitive type (number, string, date, etc.) must be "SET", not just assigned to. I always forget this whenever I go back to VBA after some extended time away.

JamesFaix Over a year ago

You can also do "inline" sets like this "Dim mySheet as Worksheet: Set mySheet = ActiveWorkbook.Sheets(1)". It won't help for your current loop situation, but can be handy.

Excel Hero Over a year ago

@CarlFriedRiceGauss Only object variables must be set and only when the New keyword is not used when the variable is dim'd. Your assertion is overly broad.

David Zemens Over a year ago

@CarlFriedRiceGauss that's technically not "inline" as the colon is parsed as a new line. I would recommend against it just for general readability sake, too :)

JamesFaix Over a year ago

@ExcelHero I thought everything that wasn't primitive was an object in VBA. Is this not the case?

Collectives™ on Stack Overflow

"Object Variable or With Block Variable not Set"

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related