0

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
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
codemia
  • 1
  • 2
    You copied entries to another sheet, but you still search inside `Sheets("Data").Range("Dyn_Full_Name")`? – GSerg Aug 06 '18 at 06:57
  • I maintained the same sheet and discarded the one I copied to – codemia Aug 06 '18 at 07:05
  • Check this named range and see where it refers to Dyn_Full_Name – Sixthsense Aug 06 '18 at 07:32
  • @Sixthsense. I never edited this code before I copied entries – codemia Aug 06 '18 at 08:04
  • 1
    Write this before the line with the error and share the result: `MsgBox Sheets("Data").Range("Dyn_Full_Name").Address` – Vityata Aug 06 '18 at 08:14
  • Named range is worksheet/workbook specific. I have not said you edited the code. What you seen in the code is the name of the named range. But actual reference of the named range may differ. Press Ctrl+F3 and check the named range reference. – Sixthsense Aug 06 '18 at 09:06
  • Also, consider that error 1004 raises with match if it finds nothing... – Foxfire And Burns And Burns Aug 06 '18 at 09:18
  • @Vityata , same error occurs again – codemia Aug 06 '18 at 09:21
  • @codemia - that's great! This means that you do not have `Dyn_Full_Name` on a sheet named `"Data"`. – Vityata Aug 06 '18 at 09:26
  • @Sixthsense here's the Reference for the Entry Form =OFFSET(Data!#REF!,0,0,Engine!$B$3,1) – codemia Aug 06 '18 at 09:28
  • Data!#REF this means that the reference is getting deleted and thats why you see #Reference in that named range. So refer your previous file and get the address of that named range and incorporate it in your current file. – Sixthsense Aug 06 '18 at 09:32
  • @Sixthsense. I have two forms on this sheet. The other one is Data_Start and references to =Data!$A$4 – codemia Aug 06 '18 at 10:28

1 Answers1

0

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • both of these are available – codemia Aug 06 '18 at 09:32
  • @codemia - what happens if you write `MsgBox ColumnD_Menu` before the error? – Vityata Aug 06 '18 at 09:34
  • When I Write MsgBox ColumnD_Menu before the error line of code and test. A Microsoft Excel Message box pops up – codemia Aug 06 '18 at 10:31
  • @codemia - this means that `ColumnD_Menu` is not correctly assigned. Write `Dim columnD_Menu as Long : columnD_Menu = 4` to assign it. – Vityata Aug 06 '18 at 11:17
  • I have tried it but the error comes back again. When I click Debug , This line of code is highlighted TargetRow = Application.WorksheetFunction.Match(ColumnD_Menu, Sheets("Data").Range("Dyn_Full_Name"), 0) – codemia Aug 06 '18 at 12:11
  • @codemia - assign some value to `columnD_Menu` and it would work. – Vityata Aug 06 '18 at 13:29