Compile Error: Sub or Function not defined

[FrankMcNally]

This works:

 Sub Enter_Click()    
Dim NextRow As Long
    Dim VA_Loop As Range
    Dim VA_Loop_Value As Range
    
    
    Sheets("_AYZ81").Activate
    NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
    Cells(NextRow, 1) = "VA" & VA_Number.Value & ".G" & G_Number
    If VA_Number.Value = 1 Then Cells(NextRow + 3, 4) = "'251-9214-8396-957-000-E"
    If VA_Number.Value = 2 Then Cells(NextRow + 3, 4) = "'251-9214-5235-958-000-E"
    If VA_Number.Value = 8 Then Cells(NextRow + 3, 4) = "'251-9214-5235-956-000-E"
    If VA_Number.Value = 9 Then Cells(NextRow + 3, 4) = "'251-9214-5235-956-000-E"

   Unload Import_Information
End Sub

BUT this does not:

    Dim NextRow As Long
    Dim VA_Loop As Range
    Dim VA_Loop_Value As Range
    
    
    Sheets("_AYZ81").Activate
    NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
    Cells(NextRow, 1) = "VA" & VA_Number.Value & ".G" & G_Number
    If VA_Number.Value = 1 Then Cells(NextRow + 3, 4) = "'251-9214-8396-957-000-E"
    If VA_Number.Value = 2 Then Cells(NextRow + 3, 4) = "'251-9214-5235-958-000-E"
    If VA_Number.Value = 8 Then Cells(NextRow + 3, 4) = "'251-9214-5235-956-000-E"
    If VA_Number.Value = 9 Then Cells(NextRow + 3, 4) = "'251-9214-5235-956-000-E"

If VA_Number.Value = 7 Then        
           Sheets("Sheet1").Activate
           Set VA_Loop_Value = sheet2.Range("H2:H9")
           For Each VA_Loop In VA_Loop_Value
            If VA_Loop = 13 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-5232-999-000-E"
            If VA_Loop = 15 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-5232-999-000-E"
            If VA_Loop = 20 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-5232-999-000-E"
            If VA_Loop = 30 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-8704-999-000-E"
            If VA_Loop = 35 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-8704-999-000-E"
            If VA_Loop = 52 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-8396-999-000-E"
            If VA_Loop = 60 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-8415-999-000-E"
            If VA_Loop = 70 Then ActiveWorksheets("_AYZ81").Cells(NextRow + 3, 4) = "'251-9214-8415-999-000-E"
        Next VA_Loop
Unload Import_Information
End Sub

The new code is supposed to read from the range created by:

ActiveSheet.Range("d2:d65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets("Sheet1").Range("h5"), Unique:=True

Whereas VA_Loop is the number within the Range of VA_Loop_Value and therefore each occurrence should generate a String

I am getting the Error:

Compile Error: Sub or Function not defined

with Sub Enter_Click() highlighted in Yellow
and
If VA_Loop = 13 highlighted in Blue

As always any and all help would be appreciated

 

[StephenCrump]

Instead of ActiveWorksheets("_AYZ81"), you need to refer to Worksheets("_AYZ81") or Sheets("_AYZ81").

Once that's fixed, you'll need an End If for this line:

If VA_Number.Value = 7 Then

 

[FrankMcNally]

Instead of ActiveWorksheets("_AYZ81"), you need to refer to Worksheets("_AYZ81") or Sheets("_AYZ81").

Once that's fixed, you'll need an End If for this line:

If VA_Number.Value = 7 Then

Thanks, it worked got ride of the error but it didn't enter any of the strings ("'251-9214-5232-999-000-E"/"'251-9214-5232-999-000-E"/"'251-9214-5232-999-000-E"...etc) on worksheet "_AYZ81"??

Any ideas

 

[FrankMcNally]

SOLVED Thanks