Function does not work within sheet

I keep getting the #VALUE! error when using this formula in my workbook and it is driving me crazy because I can't figure out why it's not working. Any thoughts?

Please Login or Register  to view this content.

I inserted a breakpoint and none of the variables have values. Since firstVal and lastVal depend on firstCell and lastCell parsing the range info correctly, there must be something wrong with my use of ".Cells(x,y)", but it doesn't seem like I am using it wrong. It has to be something stupid that I'm overlooking.

P.S. I tried simplifying it like below, but still no luck:

Please Login or Register  to view this content.

In excel, outputting somethings to cells in function should not be performed, otherwise you'll get errors.
Learn Excel along with practical examples:http://www.worldbestlearningcenter.c...se-example.htm

@darayuk, I'm not sure I follow. What do you mean by the following?

outputting somethings to cells in function should not be performed

According to my code I am not trying to output anything. I am merely trying to read what is there and parse data from a range.

Here is a sample worksheet with the error for reference. Any help would be greatly appreciated!!

CAGR.xlsm

Please Login or Register  to view this content.

You are assigning (Copying) the value of cells(1,), or Range("A1") in to firstCell varaible, but I do not see any value on cells(1,1). The same goes for the other varaibles.

Since firstVal and lastVal depend on firstCell and lastCell parsing the range info correctly, there must be something wrong with my use of ".Cells(x,y)",

Or there is something wrong with the way rng is being passed to the function. Is the reference in the function call correct? When in break mode, does rng have any information in it?

Thanks! You're right, they are unnecessary anyway. Take care!