Returning the number of cells from one method to another method.

Hi all,
First I am going to give you some code, and then describe what I am attempting to do.

Please Login or Register  to view this content.

If the value is found, this method is run from within "findCansim" to find the values and then copy them.

Please Login or Register  to view this content.

Somehow, I need the RngCount value to return to the first method so that it looks something like this(the edits are underlined):

Please Login or Register  to view this content.

The end result is to paste the values into our workbook from the found values in the source workbook. The starting cell to paste from is dependant on the number of cells being pasted. I hope this makes sense. If an example book is needed then let me know and I'll have to make one because the files are too large to upload in their current conditions.

Convert findCansim into a function that returns the relevant value. Either that or pass a variable ByRef to the FindCansim sub so that it can alter it for later use.
Note - this:

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

without parentheses.

Thanks romperstomper, I'll try as you suggested. If I pass a variable ByRef to the findCansim method, should it then look like this (given it already has a ByVal being passed to it):

Please Login or Register  to view this content.

I've never had to pass more than one argument until your suggestion so I am a little unclear.

Edit: And if I do it like this, don't I have to also pass CName again?

Yes that's correct. Calling it would be like so:

Please Login or Register  to view this content.

I'll change up some of my code to try and make this work. I'll let you know how it goes either within the next half hour (if it works for me) or sometime in the next few hours (when I am home from work). Thanks again.

After trying to figure this out by passing more than one argument through one method, I thought to myself: 'Why not just do this all in one method?' This code is probably a little crude but it only takes 13 seconds to find, copy, and paste the required data from 144 sheets.

Please Login or Register  to view this content.

I'm completely open to suggestions on improving this though but it seems to be fine.