User Defined Function Not Working Without Quotations

Hi guys, I was wondering if one of you kind fellows could lend me a hand?

I have created a function to simplify the calculation of a user specified column, however it looks first at a specified cell to find the specific worksheet to calculate, for example;

=CalcStk(F6,G)

This is telling it to look at F6 first to find out which worksheet to look at (column F contains sheet names), then calculate the total sum of everything in column G.

The code I have works, it returns the correct figure. However, it does not work unless I input the function as;

=CalcStk("F6","G")

While this works, it is useless when it comes to another macro copying and pasting the formulas, as nothing inside the quotations changes as Excel sees it as text.

Here is the code I have;

Please Login or Register  to view this content.

I know this is gonna be something stupid that i've missed, but i've been kicking myself all day trying to get it right.

Any help is greatly appreciated, many thanks!

Thanks Olly, Unfortunately this does not work, just returns the same old #VALUE error

Column F is just a list of sheet names, its looking at the specified cell for the sheet name, then using that information to set the range;

Please Login or Register  to view this content.

"page" reffers to the first variable, in this case "F6", then i'm asking it to use the value of the cell as the sheet name when setting the range.

May I kiss you sir? If that's too much I'll just hit the rep button.

This works perfectly!

Thank you so much!