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

[RebelScum]

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!

[OllieB]

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.

[Norie]

Try removing .Text after a(i,28).

[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!

[OllieB]

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.

[JosephP]

norie was correct-your sub was wrong not the function

Please Login or Register  to view this content.

you didn't need Set because you were converting a range into an array of values (and values do not have a Text property which is what caused the error)

[OllieB]

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

[Kyle123]

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.

[OllieB]

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.

[JosephP]

the code would have been better written to explicitly use the .value property

Please Login or Register  to view this content.

[Kyle123]

Indeed, I tend not to rely on default properties