Getting an error when trying to call a function from a sub, with string value of cell

I have the following code:

Please Login or Register  to view this content.

When I run this, i get "Run-time error '424': Object required" on the line where the function is called. I've tried .Text, .Value, and just the cell itself, and they all get an error.

Need help!

Thanks!

There are a few things I am not sure about. For example the statement "a=Worksheets...CurrentRegion" would declare the variable "a" as a "Range" type.However that would require the Set instruction as well, and would then invalidate the usage of Ubound which can only be used for arrays. Assuming that you want to use the ranges, I have made a few changes.

Please Login or Register  to view this content.

Thank you, Olaf. I am just learning this stuff, and got that code either from an example somewhere, or cobbled it together from several examples (which may explain why things aren't consistent).

Anyway, you got me on the right path. I got it working, but still had to make a few changes. I had to change the function to return a Double instead of Integer, and had to say GetPersonId = pl(i, 1).Value in the for loop, since I kept getting overflow errors. But after that, it works!

Thanks again!

Originally Posted by RebelScum

Thank you, Olaf. I am just learning this stuff, and got that code either from an example somewhere, or cobbled it together from several examples (which may explain why things aren't consistent).

Anyway, you got me on the right path. I got it working, but still had to make a few changes. I had to change the function to return a Double instead of Integer, and had to say GetPersonId = pl(i, 1).Value in the for loop, since I kept getting overflow errors. But after that, it works!

Thanks again!

Great that you find the remaining problems. Can you please update the title of your post to indicate that it has been solved? Thank you.

I see. Without explicit declarations and comments it is sometimes hard to understand the purpose or intention of the code.

Explicit declarations wouldn't help much here I'm afraid. Since a would just need to be declared as Variant

you can assign the values of a range to a 2 dimensional array (rather than a range) using the .Value property of the range object when the variable is a variant - it saves looping through the range to populate the array.

I understand. Had I seen that "a" was declared as a variant, I would have understood the array usage (eventually at least). Anyway, water under the bridge.

Indeed, I tend not to rely on default properties