Hello!
I'm attempting to get a Query table to filter with an array made from the values in column A of another sheet.
I'm having the worst of luck...I was pretty sure this would work but for some reason I'm getting a "type mismatch error '13'" attached to the line that says "sArray(i) = var1(i, 1)"
My code:
Could anyone tell me what I'm doing wrong?
Please Login or Register to view this content.
Last edited by neek13; 12-21-2013 at 03:36 PM. Reason: Solved !
If you step through the code does it give you that error on the first iteration of the loop?
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.Highlight the code in your post and press the # button in the toolbar.2. Show appreciation to those who have helped you by clickingbelow their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
"Slow is smooth, smooth is fast."
Hey Solus, thanks for the quick reply.
It does work for the first loop. Actually, it works up until loop 50. I think it may have to do with the fact that there are values in the column, and at a certain point the rest of the values are zero. i.e. A2:A52 have values, then A53:A60 are just zeros. Could this be where the problem lies?
Or it could be that there are more values in one array then you've dim'd for the second. You redim your array to Ubound of var but var starts from 0 not 1 so it will have at least one more value than sArray can hold.
Thanks for your help Solus! I got it to work. Instead of defining a range, I did a rows.count and added Option Base 1 to the beginning.
Here's what worked:
Please Login or Register to view this content.
Hi, I would try explicitly casting to string type usingor I would test to see if the cell errors before assigning using resume next.Please Login or Register to view this content.
FWIW there's no need for Option Base 1, var1 will be populated with a 1-indexed 2-dimensional array.
The problem was probably, as you mentioned, blank values in the array.
If posting code please use code tags, see here.
Norie, that makes a whole lot of sense. Thanks so much for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks