+ Reply to Thread
Results 1 to 17 of 17

Looping in a user defined Range of Cells

  1. #1
    Registered User
    Join Date
    10-01-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    38

    Looping in a user defined Range of Cells

    In my program, when I search currentregion for a value and then depending on a condition change the value of the cell it works, as in the following code -

    Please Login or Register  to view this content.
    However, when I want to limit the search to a smaller Range defined by me, say MyRange, the program gives error in the second line - "Object required"

    Please Login or Register  to view this content.
    Can someone guide me on how to write this code for a user defined range of cells.
    Last edited by dhatul; 06-03-2011 at 11:33 AM.
    Learning is enjoyable. Enjoying learning.

  2. #2
    Forum Contributor WinteE's Avatar
    Join Date
    04-07-2007
    Location
    Netherlands
    Posts
    544

    Re: Looping in a user defined Range of Cells

    Please Login or Register  to view this content.
    Just keep it simple !


    http://www.excelguide.eu
    In English as well as in Dutch

  3. #3
    Registered User
    Join Date
    10-01-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Looping in a user defined Range of Cells

    Quote Originally Posted by WinteE View Post
    Please Login or Register  to view this content.
    Now the error message is - Method 'Range' of Object '_Global' failed

  4. #4
    Forum Contributor WinteE's Avatar
    Join Date
    04-07-2007
    Location
    Netherlands
    Posts
    544

    Re: Looping in a user defined Range of Cells

    Sorry, forgot the quotes around the range name.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-01-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Looping in a user defined Range of Cells

    Quote Originally Posted by WinteE View Post
    Sorry, forgot the quotes around the range name.

    Please Login or Register  to view this content.
    The error remains the same.

  6. #6
    Forum Contributor WinteE's Avatar
    Join Date
    04-07-2007
    Location
    Netherlands
    Posts
    544

    Re: Looping in a user defined Range of Cells

    Is there a named range 'myrange' ?

    Insert, Name, Define

  7. #7
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Looping in a user defined Range of Cells

    Perhaps if you post your whole sub so that people here can see how you came up with MyRange. How did you declare it? How was it Set?
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Looping in a user defined Range of Cells

    Please Login or Register  to view this content.
    you can reduce a 'currentregion' very simply, using 'resize':

    Please Login or Register  to view this content.



  9. #9
    Registered User
    Join Date
    10-01-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Looping in a user defined Range of Cells

    The entire code is given below. The error message is as indicated in my earlier post.


    Please Login or Register  to view this content.

  10. #10
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Looping in a user defined Range of Cells

    I don't know if this matters but it may,
    Please Login or Register  to view this content.
    may need to be
    Please Login or Register  to view this content.
    and then
    Please Login or Register  to view this content.

  11. #11
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Looping in a user defined Range of Cells

    A few things to note:
    This line:

    Please Login or Register  to view this content.
    =
    Please Login or Register  to view this content.
    Since you want them all as integers, you should use:
    Please Login or Register  to view this content.
    VBA is stupid like that.

    I would dim myRange as a Range in this case. There may be times when it would be beneficial to dim it as a variant (I'm not sure, maybe snb could say?), but in this case I believe a Range would be better. When setting a Range variable, you have to use Set, as Mordred already said.

    Since myRange is a variable and not a named range, you don't need quotes around it in this line:

    Please Login or Register  to view this content.
    should be:
    Please Login or Register  to view this content.
    I also don't see where you declare AsOfFlag.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  12. #12
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Looping in a user defined Range of Cells

    Quote Originally Posted by davegugg View Post
    A few things to note:
    Please Login or Register  to view this content.
    Is it okay to code this as
    Please Login or Register  to view this content.
    or do you have to code it your way because it is declared a Range instead of a Variant?

  13. #13
    Registered User
    Join Date
    10-01-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Looping in a user defined Range of Cells

    Thanks Mordred. It worked. I had to make one more change (see the second line of the code below).

    Please Login or Register  to view this content.

  14. #14
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Looping in a user defined Range of Cells

    Good catch.

    Please Login or Register  to view this content.
    will do the job since myRange is a variable in our code.

    Please Login or Register  to view this content.
    would be needed if we are refering to a named range on the worksheet.

  15. #15
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Looping in a user defined Range of Cells

    But why would you....

    Please Login or Register  to view this content.

  16. #16
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,646

    Re: Looping in a user defined Range of Cells

    Please Login or Register  to view this content.
    But nothing fails if we let the VBA-compiler do it's job:

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    10-01-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Looping in a user defined Range of Cells

    Quote Originally Posted by snb View Post
    Please Login or Register  to view this content.
    But nothing fails if we let the VBA-compiler do it's job:

    Please Login or Register  to view this content.

    Heartfelt Thanks snb. Thanks for taking the trouble to explain the underlying issue in such a simple and concise manner.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1