+ Reply to Thread
Results 1 to 16 of 16

Looping Question

  1. 05-13-2011, 12:32 PM #1
    everettjsj2
    everettjsj2 is offline
    Registered User
    Join Date
    05-14-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    8

    Looping Question 

    Please Login or Register  to view this content.

    This snippet of code is supposed to run inside another for loop that loops through the integer "I" where "I" is the worksheet number. The first loop steps through all worksheets in the file and copies information from each and pastes into the first "overview" sheet. The second loop, shown above is supposed to step through the values in each row and perform a nested VLOOKUP each time. I am having trouble with the syntax for this however. I think my problem is having the sheets object inside the vlookup formula. Help would be greatly appreciated. Thank you!!
    Last edited by Leith Ross; 05-13-2011 at 01:26 PM. Reason: Added Code Tags
    Reply With QuoteRegister To Reply
  2. 05-13-2011, 01:34 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Looping Question

    Hello everettjsj2,

    Change this line
    Please Login or Register  to view this content.

    To this...
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 05-13-2011, 03:04 PM #3
    everettjsj2
    everettjsj2 is offline
    Registered User
    Join Date
    05-14-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looping Question

    Thanks Leith!

    I am still getting an error on the same line: "Error 438: Object doesnt support this property or method"

    the code is now

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 05-13-2011 at 04:51 PM. Reason: Added Code Tags
    Reply With QuoteRegister To Reply
  4. 05-13-2011, 04:33 PM #4
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Looping Question

    Try this:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 05-13-2011, 05:12 PM #5
    everettjsj2
    everettjsj2 is offline
    Registered User
    Join Date
    05-14-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looping Question

    Thanks Whizbang,

    unfortunately, I still get the same error. This is a tricky one.
    Reply With QuoteRegister To Reply
  6. 05-13-2011, 06:07 PM #6
    watersev
    watersev is offline
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Looping Question

    hi, everettjsj2, try to change Cellnum to this:

    Please Login or Register  to view this content.
    the result will be (provided N=1): CellNum = "R[9]C1"

    and then try Whizbang formula suggestion
    Reply With QuoteRegister To Reply
  7. 05-13-2011, 06:08 PM #7
    everettjsj2
    everettjsj2 is offline
    Registered User
    Join Date
    05-14-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looping Question

    Here is the entire script if anyone would like to see it.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 05-18-2011, 11:30 AM #8
    everettjsj2
    everettjsj2 is offline
    Registered User
    Join Date
    05-14-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looping Question

    Thanks Waterserv/Whizbang. I feel as though we are getting closer, but I am still getting the same error 438 "Object doesn't support this property or method". The whole program is as follows. Any more suggestions to debug this sucker would be appreciated.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 05-18-2011, 12:10 PM #9
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Looping Question

    Try this:
    Please Login or Register  to view this content.
    I still get an error due to the '$_Name' bit, but I really don't know why you have that in there. What is that for?
    Reply With QuoteRegister To Reply
  10. 05-18-2011, 12:31 PM #10
    everettjsj2
    everettjsj2 is offline
    Registered User
    Join Date
    05-14-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looping Question

    Thanks whizbang!

    That dreaded error is now gone, but has been replaced as you said by another error - 1004 "application-defined or object-defined error".

    the "$_Names" is merely the name of a worksheet which the vlookup references. 

    Please Login or Register  to view this content.
    I think the error is coming from the "Sheets(I).Name" part. im not sure if ".Name" does anything?
    Reply With QuoteRegister To Reply
  11. 05-18-2011, 12:42 PM #11
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Looping Question

    @whizbang

    Please Login or Register  to view this content.
    Last edited by snb; 05-18-2011 at 12:54 PM.

    Reply With QuoteRegister To Reply
  12. 05-18-2011, 01:07 PM #12
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Looping Question

    Quote Originally Posted by everettjsj2 View Post
    the "$_Names" is merely the name of a worksheet which the vlookup references.
    In which case you need to change '$_Name' to '$_Name'! (notice the exclamation point). Then this should work fine.

    snb,

    Ahhhh. Brilliant. Thank you very much for that.
    Reply With QuoteRegister To Reply
  13. 05-18-2011, 02:21 PM #13
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Looping Question

    As a step between my previous code and snb's, you could also use this.

    Please Login or Register  to view this content.
    snb's code is, by far, the best option. But, it takes a bit more understanding to see how it works and how to manipulate it. So, if you walk through from your code, to my first code sample, to the code in this post, and then to snb's, you can see a progressive improvement and, perhaps, extrapolate some useful techniques for future code solutions.
    Last edited by Whizbang; 05-18-2011 at 04:19 PM.
    Reply With QuoteRegister To Reply
  14. 05-18-2011, 03:56 PM #14
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Looping Question

    Hear, hear...
    Reply With QuoteRegister To Reply
  15. 05-19-2011, 03:33 PM #15
    everettjsj2
    everettjsj2 is offline
    Registered User
    Join Date
    05-14-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Looping Question

    the rotten error will not go away. I assume it is still a small syntax issue that is causing it. Thanks to all for your help, I hope we can figure this out
    Reply With QuoteRegister To Reply
  16. 05-19-2011, 03:35 PM #16
    Whizbang
    Whizbang is offline
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: Looping Question

    Which code are you using? If you are using snb's, then you need to add in the ! after '$_Name'. His code did not include that fix.

    Oops, and neither did my most recent code post.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1