Loop with And

**AG0607** · 07-31-2012, 05:11 PM

Hi,

I have a basic bit of code that I am struggling with.

I have a userform where you enter a pass number, if the pass number matches the value in column F and the value in column H is blank, it should populate the remaining fields of that row. Sounds simple enough, however, it debugs with the error message:

method 'range' of object '_global' failed

Below is the code I am using:

Please Login or Register  to view this content.

It debugs each time on the 'do until' line.

Any help will be greatly appreciated.

Thanks in advance,

Andy

---------- Post added at 10:11 PM ---------- Previous post was at 08:56 PM ----------

Hi all,

Any suggestions?

I've tried numerous pieces of code and have had no luck so any help will be appreciated.

Thanks.

**UKNinja** · 07-31-2012, 05:15 PM

What is cmdPassNum.value referencing? Can you post the workbook?

**AG0607** · 07-31-2012, 05:18 PM

cmdPassNum.value is a textbox on my userform.

Basically the value entered in there will always be between 1 - 5 and the value in column F will also be between 1 - 5.

**Mordred** · 07-31-2012, 05:19 PM

Hi Andy, I think it may be because your code doesn't know which sheet it is supposed to run this on. Try declaring the sheet name, it should be similar to the following but perhaps with a different sheet name:

Please Login or Register  to view this content.

With the sheet declared, you can now specifically call the proper ranges like

Please Login or Register  to view this content.

You should do that for all ranges, rows, columns, cells, and whatever else requires explicit sheet calls. Let me know how it goes.

**UKNinja** · 07-31-2012, 05:19 PM

Can you post the workbook?

**Richard Buttrey** · 07-31-2012, 05:20 PM

Hi,
When it debugs what is the value of 'fircount' and cmdPassNum?

What do cells F & H on the fircount row show? Are these errors?

If this doesn't help you'll need to upload your workbook.

**AG0607** · 07-31-2012, 05:20 PM

I've attached both files I am using.

However, this will debug right away as my 'submit' form feeds directly into the second workbook and will be looking for a partricular location.

Thanks in advance for your help.

**AG0607** · 07-31-2012, 05:46 PM

Originally Posted by Mordred

Hi Andy, I think it may be because your code doesn't know which sheet it is supposed to run this on. Try declaring the sheet name, it should be similar to the following but perhaps with a different sheet name:

Please Login or Register  to view this content.

With the sheet declared, you can now specifically call the proper ranges like

Please Login or Register  to view this content.

You should do that for all ranges, rows, columns, cells, and whatever else requires explicit sheet calls. Let me know how it goes.

Thanks for the suggestion, unfortunately however, I have exactly the same debug message.

Originally Posted by Richard Buttrey

Hi,
When it debugs what is the value of 'fircount' and cmdPassNum?

What do cells F & H on the fircount row show? Are these errors?

If this doesn't help you'll need to upload your workbook.

The value of 'fircount' is 1048577 and the value of cmdPassNum is 1.

The value in column F should be the same as the value in cmdPassNum and the value in column H should be blank.

Many thanks,

Andy

**Richard Buttrey** · 07-31-2012, 06:07 PM

If 1048577 is indeed the value of fircount then that's one more than Excel 2007 has rows and will be the cause of your problem.

You'll need to limit the loopcounter in some way - perhaps by detecting the last valid row.