Sub or function not defined

[Zachary902]

The code below automatically calls AddHeaderHours when something is entered into cell G30, this code does work (coded to the worksheet).

'---------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$30" Then
    Call AddHeaderHours
    End If
    
End Sub
'-----------------------------------------------------------------------

'----------------------------------------------------------------------
Sub AddHeaderHours()

   Dim MonthName As String
   Dim CropName As String
   Dim CurrentHrs As Integer
   Dim DailyHrs As Integer
   Dim Rng1 As Range
   Dim Rng2 As Range
   Dim isect As Range

     Workbook("278DH## Daily Report Template").Activate
     Workbook("278DH## Daily Report Template").Worksheets("278DH## Daily Summary").Select
     MonthName = Range("E2")
     CropName = Range("E13")
     Sheets("Crop Hours Log").Select

         Select Case CropName
         Case "Wheat"
        
         Set Rng1 = Range("E3:E14")
        
         End Select
    
         Select Case MonthName
         Case "February"
       
         Set Rng2 = Range("E4:AP4")
        
End Select

 '  ***Set isect = Application.Intersect(Rng1, Rng2)***
   isect.Value = CurrentHrs
   Range("G30").Select
   Selection = DailyHrs
   CurrentHrs = CurrentHrs + DailyHrs
   Application.Intersect(Rng1, Rng2).Select
   Selection.Paste
   End Sub
'-------------------------------------------------------------------

The line of code that is surrounded by stars gives me the error "Run-time error '5': invalid procedure call or argument". I have been tinkering with it for a while and not sure how to fix it or what it really means. If you see any other little mistakes point them out. Im still sorta new to VBA.

Thanks,

 

[JLGWhiz]

Set isect = Application.Intersect(Rng1, Rng2)

I am pretty sure it does not like "Application." since Intersect is a VBA function.

Set isect = Intersect(Rng1, Rng2)

Just looking at the code, the intersect can only be cell E4.

 

[Scott Huish]

For sure, I would stop using MonthName as a variable name, it is the name of a VBA function, and using keywords and function names as variable names can often cause problems.
I would also put some sort of check in to make sure that Rng1 and Rng2 have been assigned a range before you execute the line of code that is causing an error.

 

[Zachary902]

ok I changed my variable names so they are not quite so confusing. I still get that same error on that line after removing the "Application". Am i calling the intersection function properly?

Yes JLGWiz the intersection point is cell E4, i only have one case within each of my case statments just for the time being until I get this to work.

Thank you for the help guys

 

[Norie]

When I try the code I don't get an error where you indicate, I get it here.

     Workbook("278DH## Daily Report Template").Activate
     Workbook("278DH## Daily Report Template").Worksheets("278DH## Daily Summary").Select

Workbook should be Workbooks.

 

[JLGWhiz]

Scott probably nailed it on his post. since you are using Select Case there is a good chance that one or both of your range variables are not being initialized. You can step through the procedure using F8 function key and see If the highlight goes to those lines of code or if it skips over them. If it goes to them, then you should be able to hover the mouse pointer over the variable name and see its value in tool tips.