VBA .Find Object variable not set Error

Question

Ok, so i'm trying to create a macro that basically acts like a vlookup and searches cell by cell in column A for the same value in Column A in worksheet2, then copies all information in that row to the first open column of Worksheet 1.

Basically i have no idea what i'm doing but have it about 95% functional. My only problem is once it encounters a value from Worksheet 1 column A that it cannot find in Worksheet 2 Column A. What can i do to skip to the next value?

My If...then...else was a desperate attempt to skip over that value, clearly it did not work.

Sub ProLookUp()

Dim ColALastRow As Long
Dim ColALastRow2 As Long


ColALastRow = Worksheets(1).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow

ColALastRow2 = Worksheets(2).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow2

Dim i As Long
Dim Pro As String
Dim Pro2 As Long

For i = 1 To ColALastRow
Pro = Worksheets(1).Cells(i, 1).Value

'With Worksheets(2).Range("A1:A" & ColALastRow2)' 'ignore this part'

With Worksheets(2).Range("A1:A10000")

'the below is where my issue is, once it finds a value in column A that it
'cannot match in sheet 2 it returns the error
'Object variable or With block variable not set

If Pro = .Find(Pro, LookIn:=xlValues).Value Then
    Pro2 = .Find(Pro, LookIn:=xlValues).Row
Else
    i = i + 1
End If


    Dim LastColA As Integer
    Dim CopyRange As Range
    Dim a As Range
    Dim b As Range

        With Worksheets(2)
            LastColA = .Cells(Pro2, .Columns.Count).End(xlToLeft).Column
            Set a = .Cells(Pro2, 2)
            Set b = .Cells(Pro2, LastColA)
            Set CopyRange = Range(a, b)
        End With

    Dim PasteRange As Range
    Dim LastColumnB As Integer
        With Worksheets(1)
            LastColumnB = .Cells(i, .Columns.Count).End(xlToLeft).Column
            LastColumnB = LastColumnB + 1
            Set PasteRange = .Cells(i, LastColumnB)
            MsgBox PasteRange.Address

        End With

Worksheets(2).Select
    CopyRange.Select
    Selection.Copy
Worksheets(1).Select
    PasteRange.Activate
    ActiveCell.PasteSpecial

End With    
Next i
End Sub

David Zemens · Accepted Answer · 2016-07-28 20:37:59Z

1

I reworked some other code.

Your If statement with the i=i+1 will not do what you think it will.

I loaded the results of the find into a range variable. If the find does not find anything the resulting range variable will be Nothing. Since you can't invoke any methods on a Nothing, the error 91 raises. In order to resolve it, test for Nothing in an If block, and avoid the error.

We test to ensure that the range variable Is Not Nothing, then do the stuff. If Nothing is found then it skips the code and goes directly to the Next i.

By trying to use an if to add 1 to i will not fire the next iteration of the For loop. The code will still try to run and then iterate thus actually skipping rows.

There is no need to activate the sheets and ranges just to copy and paste.

Sub ProLookUp()

Dim ColALastRow As Long
Dim ColALastRow2 As Long


ColALastRow = Worksheets(1).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow

ColALastRow2 = Worksheets(2).Columns("A:A").End(xlDown).Row
MsgBox ColALastRow2

Dim i As Long
Dim Pro As String
Dim fnd As Range
Dim Pro2 As Long

For i = 1 To ColALastRow
    Pro = Worksheets(1).Cells(i, 1).Value

    'With Worksheets(2).Range("A1:A" & ColALastRow2)' 'ignore this part'

    With Worksheets(2).Range("A1:A10000")

        'the below is where my issue is, once it finds a value in column A that it
        'cannot match in sheet 2 it returns the error
        'Object variable or With block variable not set
        Set fnd = .Find(Pro, LookIn:=xlValues)
    End With
    If Not fnd Is Nothing Then
        Pro2 = fnd.Row
        Dim LastColA As Integer
        Dim CopyRange As Range
        Dim a As Range
        Dim b As Range

        With Worksheets(2)
            LastColA = .Cells(Pro2, .Columns.Count).End(xlToLeft).Column
            Set a = .Cells(Pro2, 2)
            Set b = .Cells(Pro2, LastColA)
            Set CopyRange = Range(a, b)
        End With

        Dim PasteRange As Range
        Dim LastColumnB As Integer

        With Worksheets(1)
            LastColumnB = .Cells(i, .Columns.Count).End(xlToLeft).Column
            LastColumnB = LastColumnB + 1
            Set PasteRange = .Cells(i, LastColumnB)
            MsgBox PasteRange.Address
        End With


        CopyRange.Copy PasteRange

    End If


Next i
End Sub

edited Jul 28, 2016 at 20:37

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

answered Jul 28, 2016 at 20:13

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

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

3 Comments

Scott Craner Over a year ago

@PeteUlrich please mark as correct by clicking the check mark by the answer. It is something only the one who posed the question can do.

Pete Ulrich Over a year ago

Updated. One more question, i noticed if column A on worksheet 2 is not sized to show the entire number it fails to match to the number on worksheet 1, any idea why that is? Should i just include a line to resize that column before starting?

Pete Ulrich Over a year ago

i just added a line to resize the column first and it's working so i'll stick with that.

Collectives™ on Stack Overflow

VBA .Find Object variable not set Error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related