+ Reply to Thread
Results 1 to 7 of 7

For..Next, offset, nested loop

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    For..Next, offset, nested loop

    I think what I'm trying to do is pretty basic, but I have no idea what I am doing when writing VBA script. I have a spreadsheet and am trying to place a spin bar in every other column and have it link to the column before it. I've got the code for that figured out.

    Please Login or Register  to view this content.
    This is working perfectly, but only for columns D and E. I have to do it for 300 pairs of columns and don't really want to have to write new code for each one of them. In the end I need the spin bar in G to link to F, spin bar in I to link to H, and so on all the way to WF). From what I understand, I'll need to do a For...next loop, and because I already have one, it will need to be nested and then also offset to work. But, I have no idea how to go about sticking all that in there to make any sense to the computer.

    It would also be great if I could get the spin bar centered in the cell rather than in the top left, but that is a minor concern compared to the rest.

    I am using Excel 2010.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,491

    Re: For..Next, offset, nested loop

    1) You should get in the habit of declaring your variables.
    2) Using the CELLS method, both the column and row references are numeric making the FOR/NEXT compatible.

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 06-04-2012 at 07:26 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: For..Next, offset, nested loop

    Hi Gingin8639,

    Try this:

    Please Login or Register  to view this content.
    (I'm only running 2003 so I guessed on the 626 for the last j value.)

    Oops - another near duplication.
    Last edited by xladept; 06-04-2012 at 07:27 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Registered User
    Join Date
    06-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: For..Next, offset, nested loop

    These both seem close, but not quite right. The code I got from JBeaucaire seems to be linking to the cell above it, but if I remove the +1 from the 8th line, it seems to work. The spinner seems is also in the cell I want the value in rather than the one before it, I like it more that way, but I'm not sure if my boss will. Is it possible to put it on the right side of the cell rather than the left? We are using a tablet computer for this data collection, when it is on the left, when you tap the arrows you can't see the value go up and down because your hand covers it. That is why I was putting the spinner in the column after the value.

    The code from XLAdept puts in the spinner in the right column, but when I click the up and down arrows, nothing appears in the cells. I'm not sure how to fix that.
    Last edited by Gingin8639; 06-04-2012 at 07:47 PM. Reason: Adding more detail.

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: For..Next, offset, nested loop

    Hi Gingin8639,

    Maybe you could post a sample spreadsheet?

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,491

    Re: For..Next, offset, nested loop

    Maybe:

    Please Login or Register  to view this content.

    ....or just take the + 1 out altogether to put the control on the right where it normally goes.
    Last edited by JBeaucaire; 06-04-2012 at 10:22 PM.

  7. #7
    Registered User
    Join Date
    06-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: For..Next, offset, nested loop

    I have included a screen shot of part of my data. We are trying to make our field data collection easier, so we are testing out entering it directly into the computer rather than having to enter it by hand. We want the spinner because hitting an arrow is much easier than having to add and then delete numbers each time you encounter a plant. We have 180 rows down filled with different plant species and will have somewhere between 250 and 350 transects going across. The edited macro JBeaucaire seemed to work when I tested it on a small section of my data so now I'm letting it run across the entire sheet. After that, I'll see if my boss approves of it. If it does, I'll mark this thread as solved. If he doesn't I might have some more questions.

    field data form.jpg

+ 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