Type-Mismatch on UBOUND(Array)

Hey Guys,

I have a dynamic named range called "Test1" which contains a list of headers for a table am trying to create through macro. Am able to assign the dynamic range to the array named "dynArr" but just getting stuck at a point where am trying to get the upper bound of the array. My code works fine if I replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Following is my code.

Please Login or Register  to view this content.

Please show me some light..

You are dealing with 1D and 2D arrays in your code.

Range >> array = 2D array

This creates a 1D array

Please Login or Register  to view this content.

This creates a Range (not an array)

Please Login or Register  to view this content.

What you need is something like this (Untested):

Please Login or Register  to view this content.

dynArr is now a 1D array because Application.transpose converts the 2D array (the range) to a 1D array

abousetta

Thank you Abousetta!

Though there were minor bugs in the code, I was able to get what you were trying to make me understand. Thank you very much! :D But I must say, the last part..

dynArr is now a 1D array because Application.transpose converts the 2D array (the range) to a 1D array

It still gives the result in 2D array. As I got "Subscript out of range" error when I tried getting the element using dynArr(i) statement.
Following is my working code:

Please Login or Register  to view this content.

Oops.. please use the following code. I have put the codes after Exit Sub for my testing purpose.. included unknowingly..

Please Login or Register  to view this content.

Range("Test1") is a range not an array

This works fine (slightly modified from your approach):

Please Login or Register  to view this content.

Yep! That's Right. Its not an array. I have been using it to get the address of the elements in the named range.
the last part in the same statement was to test if the values were really stored in the array.

Thanks for drilling down to my codes again :D