Excel Runtime Error 1004

Question

I am new to Visual Basic. I created a spreadsheet which worked fine until I copied entries from it to another sheet!

Apparently, whenever I click the CONTINUE button on the Find Entry form I get the runtime error 1004 also the drop down shows nothing in it yet I never changed the code. When I click Debug,

TargetRow = Application.WorksheetFunction.Match(ColumnD_Menu, Sheets("Data").Range("Dyn_Full_Name"), 0) 

is highlighted and I don’t know how to proceed with it as I never touched this line of code at all.

What could be the problem and how can I resolve it?

Below is my code

Option Explicit

Private Sub CommandButton1_Click()

    Dim TargetRow As Integer

    TargetRow = Application.WorksheetFunction.Match(ColumnD_Menu, Sheets("Data").Range("Dyn_Full_Name"), 0)
    Sheets("Engine").Range("B5").Value = TargetRow

    Unload Find_Entry_UF

    Data_UF.Txt_FirstName = Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value
    Data_UF.Txt_Surname = Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value


    Data_UF.Combo_Age = Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value
    Data_UF.Combo_Marital = Sheets("Data").Range("Data_Start").Offset(TargetRow, 5).Value
    Data_UF.Combo_Gender = Sheets("Data").Range("Data_Start").Offset(TargetRow, 6).Value


    If Sheets("Data").Range("Data_Start").Offset(TargetRow, 7).Value = "Yes" Then
        Data_UF.Option_Y_Children = True
    Else
        Data_UF.Option_N_Children = True
    End If

    Data_UF.Combo_Religion = Sheets("Data").Range("Data_Start").Offset(TargetRow, 8).Value
    Data_UF.Txt_Address = Sheets("Data").Range("Data_Start").Offset(TargetRow, 9).Value
    Data_UF.Combo_FileNumber = Sheets("Data").Range("Data_Start").Offset(TargetRow, 10).Value

Answer 1

The 1004 error comes from the fact, that in the Excel file at least 1 of the following 2 conditions are missing:

• There is no sheet named Data
• There is no named range Dyn_Full_Name on the sheet Data

Adjust the worksheet correspondingly and error would be fixed.