object variable or with block variable not set 91 VBA

Question

I'm working on a macro that should transfer a data into a different sheet named "pistoia" and this is the code:

Sub SetCounter(ByVal counter As Double, ByVal product As String)
Dim ws As Worksheet
On Error Resume Next
Sheets("pistoia").Activate
Set ws = ActiveWorkbook.Sheets("pistoia")
On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "pistoia sheet not found"
    Else
        If ws.Name = ActiveWorkbook.ActiveSheet.Name Then
            Dim index_destRow As Integer, index_destColumn As Integer, search_product As Range
            Rows(2).Find(What:="Nome commerciale", LookAt:=xlWhole, LookIn:=xlValues).Offset(2).Select
        Else
            MsgBox "pistoia sheet found but is inactive"
        End If
    End If
End Sub

The error raises at the line: "Rows(2).Find(What:="Nome commerciale", LookAt:=xlWhole, LookIn:=xlValues).Offset(2).Select", I think that the error is due to the activation of the new sheet because in a previous macro made "on the starting sheet" I run this same operation in the row that caused the error. Any suggestions?

Add ws. in front of Rows(2). Assigning parentage to an object ensures that the call acts on the desired specified object. The other thing is to make sure "Nome commerciale" exists where you are looking for it. — Scott Holtzman
I tried with the ws. but nothing changed...and yes I've checked the row 2 — Jacopo Mansani
what if you do add debug.print in front of the command and change .Select to .Value. Does the error persist? Or does it return a value in the immediate window? — Scott Holtzman
the error persist and nothing is written on the immediate window — Jacopo Mansani

SJR SJR · Accepted Answer · 2016-10-19T15:07:08

That suggests to me that the value is not being found so that it is trying to select something doesn't exist. E.g. set a range variable to check the value is found. With Find, it's also worth specifying some of other parameters in case they are not what you expect.

    Sub SetCounter(ByVal counter As Double, ByVal product As String)

    Dim ws As Worksheet, index_destRow As Integer, index_destColumn As Integer, search_product As Range
    Dim rFind As Range

    On Error Resume Next
    Sheets("pistoia").Activate
    Set ws = ActiveWorkbook.Sheets("pistoia")
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "pistoia sheet not found"
    Else
        If ws.Name = ActiveWorkbook.ActiveSheet.Name Then
            Set rFind = ws.Rows(2).Find(What:="Nome commerciale", LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                rFind.Offset(2).Select
            Else
                msgbox "Value not found"
            End If
        Else
            MsgBox "pistoia sheet found but is inactive"
        End If
    End If

    End Sub

object variable or with block variable not set 91 VBA

1 Answers