Macro to write custom function into cells

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Neil, thanks for the replies. See whole function below:

Please Login or Register  to view this content.

Thanks,

Rob

Neil,

Just playing around with this again. The problem seems to be when the IFERROR is true. Works ok (if probably a little inefficiently) when the hours function doesn't return an error

Ok think I might have sorted this out.

I've added "On Error Resume Next" on the first line of the function, it seems to run ok now.