Array loop producing error "Run time error '1004'"

**jeskit** · 05-06-2011, 03:52 AM

Hi

I have created the array loop below, which should go through the array and put each letter within in the place of the column letter within the lines of code.

Please Login or Register  to view this content.

However when i run it i get an error of "Method 'range' of 'object' '_global' failed"

Does anyone know why i get this error?

Thanks

Jeskit

**snb** · 05-06-2011, 05:15 AM

Array("F") isn't an array
Avoid using 'select' in VBA-code
Probably you have a namedrange "F" (your information is rather scarce)
If you implicitly declare an object variable 'sht2' it isn't obvious to refer to a variable 'sht3' in the code that follows.
This could be your intention ?

Please Login or Register  to view this content.

**jeskit** · 05-06-2011, 05:54 AM

hi

Thanks for replying. All working now!! However do you know if its possible to have two arrays in the same for loop??

As i need one array for the column letter, but also need one for the column number in vlookup formula.

Please Login or Register  to view this content.

**snb** · 05-06-2011, 06:20 AM

Please read/study my post again to avoid 'select'.

**jeskit** · 05-06-2011, 06:25 AM

HI

I have taken the first select statement out and placed it before hand but the second one can not be removed as the selection autofill doesn't work without it.

**snb** · 05-06-2011, 06:51 AM

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

**jeskit** · 05-06-2011, 07:13 AM

Hi

Would you code work if the array variable was placed inside the vlookup?

Like?

Please Login or Register  to view this content.

**snb** · 05-06-2011, 07:24 AM

You can test it; I can't.
Sure it's possible, but since you gave no information about what you want to achieve I can't say whether that will result in what you're aiming at.

**jeskit** · 05-06-2011, 07:34 AM

Hi

I tested it and itstead of a value i got #Value error in the cell.

Basically i need to put a vlookup forumla in cells F2, K2, W2, X2 and Y2 which looks up the value in Cell A1 in the Range ConvertManual!$A$2:$Y$500 in columns 6, 11, 23, 24 and 25.

Then once its placed them in the columns do an autofill on columns F2, K2, W2, X2 and Y2.

This can be done by writing the same line of code for each column and then another line of code to autofill each column but i was hoping i could avoid that and use an array but it didn't seem to work within the Vlookup forumla.

**snb** · 05-06-2011, 08:17 AM

The proof

Please Login or Register  to view this content.

**jeskit** · 05-06-2011, 08:22 AM

Hi

I tried it and it works except it keeps putting the an N/A# in the cell above F2

**snb** · 05-06-2011, 08:50 AM

- did you catch the difference between your code and mine and why you can't put a variable in a string just inserting its name ?
- so what's the value in A1 ? that could explain why...., etc.

**jeskit** · 05-06-2011, 08:56 AM

Hi

I have it working now i forgot about the headerline so it should have been looking at A2 not A1.

Thanks for your help. I greatly appricated it

Array loop producing error "Run time error '1004'"

LinkBack

Thread Tools

Rate This Thread

Display

Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Re: Array loop producing error "Run time error '1004'"

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions