CODEWITHSUNDEEP
excelruntime-errorvba
rbonac
rbonac

Reputation: 125

Error '91': Object variable or With block variable not set

I am currently trying to alter some prewritten code and therefore created a dummy file. As I am trying to run some test samples I encounter the following error:

Run-time error '91': Object variable or With block variable not set

You find the code extract below.

Sub Button1_Click()

Dim wb As Workbook
Dim ws As Worksheet
Dim count As Integer

count = 0
Do While CDate(ws.Cells(2 + count, 1).Value) <= CDate(DateAdd("d", 14, Now()))
    count = count + 1
    ws.Range("A" & count + 1).Interior.Color = RGB(250, 50, 50)
    If CDate(ws.Cells(1 + count, 1).Value) <> CDate(ws.Cells(1 + count, 2).Value) Then
        If ws.Range("C" & count + 1) <> "In Sub" Then
            ws.Range("C" & count + 1).Interior.Color = RGB(250, 50, 50)
        Else
            ws.Range("C" & count + 1).Interior.ColorIndex = 44
        End If
    End If
Loop
...

End Sub

The datasheet looks as follows:

Issue Date  Maturity    Status  ISIN            Price
25/01/2013  01/01/2020  Issued  XS0879579182    88
06/02/2018  02/01/2020  In Sub  XS1515113535    99
31/01/2018  03/01/2020  Traded  XS5445656466    87
18/01/2018  04/01/2020  Issued  XS0254521554    100
20/01/2018  05/01/2020  Issued  XS5458614653    98
19/01/2018  06/01/2020  Issued  XS2375645421    97
12/01/2015  07/01/2020  Issued  XS4158674165    92

I guess I did not define the objects correctly, may anyone please help me with this problem?

Thanks in advance! Kind regards

Upvotes: 0

Views: 3791

Answers (1)

bilpor
bilpor

Reputation: 3911

After the declaration you need to do something like:

Set wb = ActiveWorkbook
Set ws = wb.Sheets("name")
' if wb is other than the active workbook
 wb.activate
 ws.Select

Upvotes: 1

Related Questions