how do I deal with error 1004 in vba excel ?

Question

Hi,

Basically I have 3 worksheets . From the first worksheet I get a value (rotid) , and by using the match function I try to find the value in the second worksheet , and copy the row associated with the value into worksheet 3 where y is the index of the row in sheet 2 which corresponds to the value (rotid). But sometimes a value is not found in worksheet 2 and the program crashes . How can I deal with this error ?

worksheet 1 is list,
worksheet 2 is rotmain2,
worksheet 3 is imdb

btw this is how my code looks like . Im not sure how to use codetags.

Sub combine_imdb_rot_data()

' y is the index of the row in rotmain2 which corresponds to rotid

Dim y As Variant

Sheets("imdbmain").Select

For i = 1 To 4415

  ' select sheet list and assign rotid for value of the cell in row i+1  and column 7
  rotid = Sheets("list").Cells(i + 1, 7).Value

  ' if rotid is not empty,
  If Len(rotid) > 0 Then

    'look for a row that corresponds to the rotid in worksheet "rotmain2"
    Sheets("rotmain2").Select
    'x = Sheets("list").Cells(i + 1, 7).Row
    y = WorksheetFunction.Match(rotid, Range("B1:B4218"), 0)

    If IsNumeric(y) Then                             
        ' copy the information in the row             
        Range(Cells(y, 1), Cells(y, 13)).Select
        Selection.Copy                   
        ' paste it into row i+1 in worksheet "imdbmain"
         Sheets("imdbmain").Select
         'select row i+1 in imdbmain
        Range(Cells(i + 1, 9), Cells(i + 1, 21)).Select
        Workbooks(1).Sheets(1).Paste
        Application.CutCopyMode = False
    Else
        ' copy the information in the row                  
        Range(A4220, M4220).Select
        Selection.Copy

        ' paste it into row i+1 in worksheet "imdbmain"
         Sheets("imdbmain").Select
         'select row i+1 in imdbmain
        Range(Cells(i + 1, 9), Cells(i + 1, 21)).Select
        Workbooks(1).Sheets(1).Paste
        Application.CutCopyMode = False
    End If   
End If
Next
End Sub

I also tried with another method suggested by Remou. Is this how the .find method works ? Im not sure but when I use it I get a Runtime error 13 type mismatch:

Sub combine_imdb_rot_data()

' y is the index of the row in rotmain2 which corresponds to rotid

Dim y As Variant

Sheets("imdbmain").Select

For i = 1 To 4415

' select sheet list and assign rotid for value of the cell in row i+1  and column 7
rotid = Sheets("list").Cells(i + 1, 7).Value

' if rotid is not empty,
If Len(rotid) > 0 Then

    'look for a row that corresponds to the rotid in worksheet "rotmain2"
    Sheets("rotmain2").Select
    'x = Sheets("list").Cells(i + 1, 7).Row
    Set y = Range("B1:B4218").Find(rotid)


    If y Is Nothing Then

        ' copy the information in the row
        'Range("1:4," & x & ":" & x).Copy
        'Range("A&x"&:&"M&x").Copy
        'Copy empty row

        Range("A101:M101").Select
        Selection.Copy


        ' paste it into row i+1 in worksheet "imdbmain"
         Sheets("imdbmain").Select
         'select row i+1 in imdbmain
        Range(Cells(i + 1, 9), Cells(i + 1, 21)).Select
        Workbooks(1).Sheets(1).Paste


    Else

        ' copy the information in the row
        'Range("1:4," & x & ":" & x).Copy
        'Range("A&x"&:&"M&x").Copy

        Range(Cells(y, 1), Cells(y, 13)).Select
        Selection.Copy


        ' paste it into row i+1 in worksheet "imdbmain"
         Sheets("imdbmain").Select
         'select row i+1 in imdbmain
        Range(Cells(i + 1, 9), Cells(i + 1, 21)).Select
        Workbooks(1).Sheets(1).Paste
        Application.CutCopyMode = False




    End If

End If Next

End Sub

Answer 1

You could trap the error, or perhaps use Find:

rotid=5 ''Let us say

With Worksheets(1).Range("B1:B4218")
    ''Find returns a range object, so we use Set
    Set y = .Find(rotid, LookIn:=xlValues, lookAt:=xlWhole)
    If y Is Nothing Then
        Debug.Print "Not found"
    Else
        ''This will print a cell, $b$15, for example
        Debug.Print y.Address
        ''This will print 5
        Debug.Print y.Value
    End If
End With

Futher information: http://msdn.microsoft.com/en-us/library/aa195730%28office.11%29.aspx

Answer 2

btw is it possible to use the find method without the with statement ? I tried it without With Worksheets(1).Range("B1:B4218") statement , since I would like to loop over a list of cells but then I get Invalid or Unqualified refereence ? What is that ?

Sub combine_imdb_rot_data()

' y is the index of the row in rotmain2 which corresponds to rotid

Dim y As Variant

Sheets("imdbmain").Select

For i = 1 To 4415

' select sheet list and assign rotid for value of the cell in row i+1  and column 7
rotid = Sheets("list").Cells(i + 1, 7).Value

' if rotid is not empty,
If Len(rotid) > 0 Then

    'look for a row that corresponds to the rotid in worksheet "rotmain2"
    Sheets("rotmain2").Select
    'x = Sheets("list").Cells(i + 1, 7).Row

    Set y = .Find(rotid, LookIn:=xlValues, lookAt:=xlWhole)


    If y Is Nothing Then

        ' copy the information in the row
        'Range("1:4," & x & ":" & x).Copy
        'Range("A&x"&:&"M&x").Copy

        Range(Cells(y, 1), Cells(y, 13)).Select
        Selection.Copy


        ' paste it into row i+1 in worksheet "imdbmain"
         Sheets("imdbmain").Select
         'select row i+1 in imdbmain
        Range(Cells(i + 1, 9), Cells(i + 1, 21)).Select
        Workbooks(1).Sheets(1).Paste
        Application.CutCopyMode = False


    Else
        ' copy the information in the row
        'Range("1:4," & x & ":" & x).Copy
        'Range("A&x"&:&"M&x").Copy
        'Copy empty row

        Range(A4220, M4220).Select
        Selection.Copy


        ' paste it into row i+1 in worksheet "imdbmain"
         Sheets("imdbmain").Select
         'select row i+1 in imdbmain
        Range(Cells(i + 1, 9), Cells(i + 1, 21)).Select
        Workbooks(1).Sheets(1).Paste
        Application.CutCopyMode = False




    End If

End If Next

End Sub