VBA Data Type Error

I'm trying to write my first custom function, and I've hit roadblock after roadblock. I found answers for all except this last error message. After this, my function should be finished.

I've found the line that prompts the error, and I've got no clue as to why it won't work. Here's the last snippet of my code. (the 2 dimension table array and entry variable were declared higher up in the code and they've been working fine)

Please Login or Register  to view this content.

Running my code with this as shown returns the #VALUE! error (A value used in the formula is of the wrong data type). Now if I remove the "counter = counter + 1" line, then the code runs properly. I've tried making the counter variable every possible data type in some last-ditch effort, but with no luck. Why would this not work? Any insights would be greatly appreciated.

Can you post the entire function and/or upload a sample workbook?

the #VALUE! error (A value used in the formula is of the wrong data type).

I'm not sure I would assign this kind of specific meaning to a spreadsheet #VALUE error. In my experience with UDF's, the #VALUE error really means "I came across some kind of untrapped runtime error that I could not resolve so I will put #VALUE here instead". This could be a data type mismatch error, or it could be a syntax error, or it could be an untrapped divide by 0 error or whatever.

You indicate that removing the counter=counter+1 statement "resolves" the error. Since all counter is doing is specifying what element from table(i,j) to return, I would guess that counter becomes a value that is incompatible with the table() array (probably why Norie wants to see how table() is defined and filled). I think the next thing I would do would be to add a stop statement (probably right before counter is initialized), then step through the loop -- paying careful attention to what counter, table, arr, entry, and any other variables are doing using the locals and watch windows.

Please Login or Register  to view this content.

If I uncomment this then the function will return #VALUE!.

Please Login or Register  to view this content.

This is because of a subscript out of range error here,

Please Login or Register  to view this content.

which is caused by counter02 exceeding the bounds of the array table.

Not sure how to fix the function because, to be honest, I'm not sure what it's meant to be doing.