Sub or function not defined

Zachary902

New Member
Joined
Nov 5, 2013
Messages
7
The code below automatically calls AddHeaderHours when something is entered into cell G30, this code does work (coded to the worksheet).

Code:
'---------------------------------------------------------------------
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,
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Set isect = Application.Intersect(Rng1, Rng2)
I am pretty sure it does not like "Application." since Intersect is a VBA function.
Code:
Set isect = Intersect(Rng1, Rng2)

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 5, 2013
Messages
7
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
When I try the code I don't get an error where you indicate, I get it here.
Code:
     Workbook("278DH## Daily Report Template").Activate
     Workbook("278DH## Daily Report Template").Worksheets("278DH## Daily Summary").Select
Workbook should be Workbooks.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top