Why Would Range() Apply Different Data Types to My Data?

Hello VBAmasters,

Recently I posted a problem having to do with looping through two different columns at the same time. (As a newbie forum member, I'm forbidden to include URLs in my post, sorry.) Another forum member helped me out, and posted some code; here's the relevant snippet:

Please Login or Register  to view this content.

This code ran on a spreadsheet where columns B and C were populated with text strings:

Diagram 1.jpg

And the code worked great. When I turn on the debugger and set a watch to the "Girls" and "Tasks" variables, I see what I guess is an array of strings; exactly what I want:

Diagram 2.jpg

The example code works perfectly.

Now to be honest, I am working on a very large spreadsheet with a lot of VBA code. The "Loop through two columns" issue I posted about was smaller problem in that code. For posting purposes, I deliberately created a little toy spreadsheet that illustrated the looping problem. I thought that if someone would post a solution, I could adapt that solution to my "real" spreadsheet. Here's the adaptation:

Please Login or Register  to view this content.

There's not much that is different. I changed the variable names and the two columns I need to loop through are on separate worksheets. I didn't think that would pose any problems. The columns look like this; here's "Peers", and then "ProtoCmds":

Diagram x.jpg

Like the toy version, these columns are just strings, nothing more. I want Excel and VBA to see them as text strings. And when the Range() command runs in my VBA code, I want the function to return an array of strings.

So when I run my adapted version of the code, VBA throws a "Run-time error '13' :: Type mismatch" error, which basically means I'm feeding Ubound() the wrong data types. I set a debug watch on renamed variables "Peers" and "ProtoCmds" and sure enough, they are NOT arrays of strings:

Diagram 7.jpg

So, "Peers" seems to be an array of... empty variants? And worse, "ProtoCmds" appears to be a single string. Not an array. No wonder Ubound() choked when it was fed this variable. I wish I knew why Range() thought it was looking at one cell, when there are at least a hundred cells of strings in that column. Perhaps the data in the first cell ("!========...") fools it or something???

So I'm just guessing here, but I wonder if when Range() is called, it looks at the data and makes an educated guess about the data type it is looking at. In my toy spreadsheet, it correctly guessed that I wanted an array of strings. In my "real" spreadsheet, it incorrectly guessed and gave me the wrong data types.

Is this what is going on here? Why might Range() be misreading my data? Is there was way to force Range() to return the data types I want? Thanks so much for reading.

There is one problem and one possible problem.

1. What if the number of rows in each column is different?
2. The 2nd part of the range also needs a worksheet object qualifier. If not set, it uses the activesheet.
e.g.

Please Login or Register  to view this content.

Do that for the other as well.

Please Login or Register  to view this content.

2 question:
1. Cells(Rows.count, 2) means last row in the sheet in col B not D (2 is col B), is that what you want?
2. Cells(Rows.count, 2), because you don't qualify the cell and the row then it mean the code will use the cell & rows in the active sheet not in sheet Worksheets("Variables").

If what you want is to get the range in Worksheets("Variables").Range("D26:D" to last row with data), then:

Please Login or Register  to view this content.

And the same problem with:

Please Login or Register  to view this content.

Thanks Kenneth,

Good catch of the lack of a Worksheet qualifier on the "Peers" variable. I'd completely missed that.

The number of rows in the "Peers" and "ProtoCmds" are not related and will almost certainly not be the same. I'll need the VBA to dynamically work with both columns, regardless of how long each are. (I think its also safe to assume neither column will be longer than 100 rows.)

Its late where I am (New Jersey), so I'll add the qualifiers first thing in the AM and retest. Thanks so much!

Wow, Akuini, you're good!

1) No, I definitely mean to define "Peers" as the rows D26, D27, D28... D-something. Never straying into Column B.

2) You're correct that my code is working off of another active worksheet when it runs the lines I quoted in my original post. I hadn't thought that I was grounding the beginning of my range on Worksheet "Variables" but not grounding the end.

It is late where I am and I need to get to bed. I will test your fixes in the morning and post responses. Thank you for your help!

Hi Kenneth,

Yep, you were right, I wasn't properly referencing the end of the range. Plugging that did the trick. Thanks!

Thanks Akuini,

Yep, you spotted the problem. Thanks, I'm good to go! Appreciate the help...!

You're welcome, glad to help, & thanks for the feedback.