Hi

I've created a custom function call 'hours' that I've put into every cell in a large table. It uses two variables 'project' and 'plan_date'. See first part of this below:

Public Function Hours(project, plan_date As Date)

'Define the array of data to be used in function

Dim scheme_data As Variant
scheme_data = Sheets("TEST").Range("B2:P100")

'Define key dates of stages based on 'project'

Dim Gate_A2, Gate_B, Gate_C, ACL As Date

Gate_A2 = DateValue(Application.VLookup(project, scheme_data, 5, False))
My problem is that I don't want this to automatically recalculate, as it slows everything down, but I don't want to switch the whole workbook to manual calculation as others have to use it.

I thought I'd write a separate macro to enter the formula into the cells then paste special to just store the values in the table. The idea being that I'd call this macro every time I wanted to update the table.

Below is the code I tried just in one cell

    ActiveCell.FormulaR1C1 = "=IFERROR(Hours(RC2,R2C),0)"
When I run this, I get runtime error '13' type mismatch. The debugger takes me to the line in the function where Gate_A2 is defined. This is the first time that 'project' is referenced so I'm guessing that there's some issue with the order that excel is doing things and it isn't picking up that 'project' is defined by the function.

The formula works fine if I type it in manually.

Hope this makes sense, any ideas how I can get rid of the runtime error?

Thanks,

Rob