Unable To Interate Through Cells In A Named Range

**Samuel K** · 05-17-2012, 11:36 AM

I'm trying to what I assumed would be a simple task, based on the fact that I've done very similar things in the past, but is coming up with an error I can't figure out. I have a combobox which I fill from a dynamic named range. When I select an item in the combobox, I want it to iterate through that range and then put the data from the next column into a text box.

I get the generic Run-time error '1004': Application-defined or object-defined error on the bolded line of code. (This happens on the first iteration through the loop.)

CurRow is set to 2 and CurCol is set to 1, which is the correct location for the first row of the cell. I'm sure the range is setup correctly because the combobox populates from it using the RowSource property and PartList is definitely the correct name for the sheet.

What am I missing that could be causing this error?

Please Login or Register  to view this content.

**JieJenn** · 05-17-2012, 11:38 AM

Instead of Empty shouldn't that be "Empty" with double quotation mark?

**Samuel K** · 05-17-2012, 11:44 AM

No, Empty is a recognized keyword to signify a cell that has nothing in it.

**Andy Pope** · 05-17-2012, 11:52 AM

try using Long variables rather than string ones.

Please Login or Register  to view this content.

**Mordred** · 05-17-2012, 11:53 AM

Why have you decided to use the Value2 property?

**Mordred** · 05-17-2012, 11:54 AM

Good spot Andy, I didn't notice that until you pointed it out.

**Samuel K** · 05-17-2012, 11:59 AM

Originally Posted by Andy Pope

try using Long variables rather than string ones.

Ugh. That was it. For some reason, in an old project where I used similar code, I had declared them as strings initially and didn't realize that at some point I had redeclared them as integers. Thank you very much.

Originally Posted by Mordred

Why have you decided to use the Value2 property?

To be honest, I'm not sure. I know I ran into some problem in an older project where I was required to use Value2 but I don't remember what that problem was anymore.

I tested this new project with just .Value and it seems to work fine. (I'm predominately a VB.NET/PHP programmer, I've only recently dabbled in Excel/VBA in order to fix a couple of applications my company has been using far longer than I've been here.)