find method vba run-time error 91

Question

There is a problem with the find method in below code.

c = Cells.find(wValue).Address

i always get run-time error 91: object variable or with block variable not set. It worked perfectly when i tried it in a trial code without loop, but i can't really figure out, what exactly i need to change in order to make it work.

Do you have any suggestions on that?

Sub find()

Dim cRange As Range, rngQty As Range, z As Range
Dim Date1 As Integer
Dim c As String

With Application
        .ScreenUpdating = False
        .EnableEvents = False
End With

Set wkbZ = Workbooks("Order History.xlsm")
Set wkbY = Workbooks("Forecast Form.xlsm")

For Each z In wkbZ.Sheets("2015").Range(Range("A2"), Range("A2").End(xlDown))

Set rngQty = z.Offset(, 3)
Date1 = Month(z.Offset(, 4))

wValue = z.Value

wkbY.Activate

c = Cells.find(wValue).Address

Set cRange = Range(c)

cRange.Select

If Date1 = 1 Then
    Set rngPaste1 = Selection.Offset(, 3)
End If
If Date1 = 2 Then
    Set rngPaste = cRange.Offset(, 4)
End If
If Date1 = 3 Then
    Set rngPaste = cRange.Offset(, 5)
End If
If Date1 = 4 Then
    Set rngPaste = cRange.Offset(, 6)
End If
If Date1 = 5 Then
    Set rngPaste = cRange.Offset(, 7)
End If
If Date1 = 6 Then
    Set rngPaste = cRange.Offset(, 8)
End If
If Date1 = 7 Then
    Set rngPaste = cRange.Offset(, 9)
End If
If Date1 = 8 Then
    Set rngPaste = cRange.Offset(, 10)
End If
If Date1 = 9 Then
    Set rngPaste = cRange.Offset(, 11)
End If
If Date1 = 10 Then
    Set rngPaste = cRange.Offset(, 12)
End If
If Date1 = 11 Then
    Set rngPaste = cRange.Offset(, 13)
End If
If Date1 = 12 Then
    Set rngPaste = cRangec.Offset(, 14)
End If

rngPaste.Value = (rngPaste.Value) + (rngQty.Value)

wkbZ.Activate

Next

With Application
        .ScreenUpdating = True
        .EnableEvents = True
End With

End Sub

Do you get the error on the first iteration of the loop or an the second? And what value does z.Value return? — Kajkrow, Aug 01 '18 at 05:56
If `Find()` fails to make a match then it returns `Nothing`, so you need to test for that first — Tim Williams, Aug 01 '18 at 05:59
wkbZ.Sheets("2015") needs to be the activesheet when you run that or your for ... next will fail. see [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Aug 01 '18 at 05:59
`Set rngPaste = cRangec.Offset(, Date1 + 2)` you don't need all those `If` blocks! — Tim Williams, Aug 01 '18 at 06:02

score 0 · Accepted Answer · answered Aug 01 '18 at 06:50

Untested but should be close...

Sub find()

    Dim cRange As Range, rngQty As Range, z As Range
    Dim Date1 As Integer
    Dim rngSrch As Range
    Dim wkbZ As Workbook
    Dim wkbY As Workbook

    Set wkbZ = Workbooks("Order History.xlsm")
    Set wkbY = Workbooks("Forecast Form.xlsm")

    'fully-qualify all Range calls with a worksheet object
    With wkbZ.Sheets("2015")
        Set rngSrch = .Range(.Range("A2"), .Range("A2").End(xlDown))
    End With

    For Each z In rngSrch.Cells

        Set rngQty = z.Offset(0, 3)
        Date1 = Month(z.Offset(0, 4))

        'better to specify whether looking at full content or part..
        'also not clear which sheet you're searching?
        'notice no select/activate needed...
        Set cRange = wkbY.Sheets(1).Cells.find(what:=z.Value, lookat:=xlWhole)
        If Not cRange Is Nothing Then
            With cRange.Offset(0, Date1 + 2)
                .Value = .Value + rngQty.Value
            End With
        End If

    Next

End Sub

Man, you are the best. It runs so smooth. Thanks a lot! – Zeno Graue Aug 01 '18 at 07:12 — Zeno Graue, Aug 01 '18 at 07:12

find method vba run-time error 91

1 Answers1