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
– Scott Holtzman, Commented Oct 19, 2016 at 15:05
I tried with the ws. but nothing changed...and yes I've checked the row 2 — Jacopo Mansani
– Jacopo Mansani, Commented Oct 19, 2016 at 15:39
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
– Scott Holtzman, Commented Oct 19, 2016 at 15:59
the error persist and nothing is written on the immediate window — Jacopo Mansani
– Jacopo Mansani, Commented Oct 19, 2016 at 16:21

SJR · Accepted Answer · 2016-10-19 15:35:16Z

2

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

edited Oct 19, 2016 at 15:35

answered Oct 19, 2016 at 15:07

SJR

23.1k6 gold badges21 silver badges26 bronze badges

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

7 Comments

Darren Bartrup-Cook Over a year ago

Could you expand on that, please - maybe an example of how to correct it (probably the Select statement and checking the reference is not nothing)...

SJR Over a year ago

Yes, see above.

Jacopo Mansani Over a year ago

I used the strategy laid out bu now I'm getting error 448 named argument not found, but in row 2 and column 1 of the Sheet "pistoia" there's just the text "Nome commerciale"...

Jacopo Mansani Over a year ago

solved the error 448...Mac version doesn't support Searchformat...now I'm getting "Value not found" but in A2 there's the label that I search...no idea why it doesn't find the match.

SJR Over a year ago

Is it definitely on the right sheet? Check there are no leading or trailing spaces as you are looking at the whole cell contents.

|

Collectives™ on Stack Overflow

object variable or with block variable not set 91 VBA

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related