cell references in UDF

[thefrogsaysquack]

I recently wrote a function that adds a bunch of email addresses together and separates them with commas. I made it dynamic because the number of email addresses in each section is constantly changing. There is a sheet attached so you can see what I'm talking about. There are more rows above and below what I attached, but they are insignificant. Here is the code that I wrote:

Please Login or Register  to view this content.

The code works just fine when the range in the function is on the same sheet as the function. But for some reason, if I try to use the function in sheet 2 for example and the email addresses are in sheet 1, I get #value!.......any ideas as to why?

[xladept]

Hi thefrogsaysquack,

It looks as though you haven't coded:

Please Login or Register  to view this content.

[thefrogsaysquack]

Sorry, I don't quite understand what you're trying to say

[tigeravatar]

thefrogsaysquack,

Attached is a modified version of your posted workbook. In Sheet2 cell B1 and copied down is this formula:

Please Login or Register  to view this content.

I modified your UDF quite a bit. It's good practice to have strongly named (easily identifiable) variable names and they should be declared at the top of any function or subroutine. I used an array to store the data values from the range that is fed to the UDF, so that it doesn't matter what sheet you use the UDF on.

Here is the modified UDF code:

Please Login or Register  to view this content.

[thefrogsaysquack]

Thanks for the reply. Honestly, I have no idea what you wrote lol. It works in the sense that it completes the task, but it adds a comma at the end of the final string if there are empty cells in the range. suggest?

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[thefrogsaysquack]

works perfectly. Thanks.

[tigeravatar]

You're very welcome