+ Reply to Thread
Results 1 to 4 of 4

Looping through rows then columns

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Looping through rows then columns

    Hi I'm trying to create a macro to help with my data.

    I have a column "Rankw" on sheet "Data" that has values ranging from -1711 to 1815 ish.
    On another sheet "Workings" i want to populate cells Min (B3) and Max (B4) with the values from column "Rankw". By changing the values of Min and Max i change the range of values my sumif formula uses to give me a value in H4 on "Workings"

    I'd like to copy the value of H4 for each instance of changing Min and Max to a sheet called "Data2"

    So my results would be something like:

    Min -1711 Max 1815 H4= 0
    Min -1711 Max 1669 H4= 3
    Min -1711 Max 1618 H4= 3
    .
    .
    .
    .
    .

    .
    Min 1815 Max 1618 H4= 1
    Min 1815 Max 1669 H4= 1
    Min 1815 Max 1815 H4= 2

    For ease so far i have started by creating a grid with the values from column "Rankw" from -1711 to 1815 in column A on "Data2" and values -1711 to 1815 in row 1.

    I've tried using solver using a loop that runs through the first column going down row by row and pasting the value of H4 into the cells B2:B806, but how do i get the loop to go onto the next column and starting again in row 2, then repeating until upto column ADZ (805 columns in total)?


    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,510

    Re: Looping through rows then columns

    Not sure I understands your problem properly but it seems to me that this is what you wish to paste with an row and column offset.

    Please Login or Register  to view this content.
    Then you need two "For" loops

    Please Login or Register  to view this content.
    First j = 0 , then macro loops through all i values from i = 0 to i 804, it then moves down to "Next j" and changes j to 1, loops though all i values, increases j to 2 and so forth.

    Alf

  3. #3
    Registered User
    Join Date
    01-03-2018
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Looping through rows then columns

    Thanks Alf,

    I didn't see your post but that's exactly what i did, and got rid of the solver code as i was making it more complicated than it needed to be.

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Looping through rows then columns

    if can please provide a sample file. (remove any sensitive data)

    get advance > manage attachment > upload file > close window

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Looping through worksheets, columns and rows
    By DaveKimble in forum Excel General
    Replies: 4
    Last Post: 09-01-2014, 02:04 AM
  2. Help Looping Through Excel Columns & Rows
    By Mark123456789 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2013, 07:44 PM
  3. Looping Through Columns And Rows
    By slimjm911 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2012, 08:41 PM
  4. Looping through rows and columns at the same time
    By omgeokid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2011, 12:02 PM
  5. Looping through rows & condensing columns
    By nessyness in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-12-2010, 03:02 PM
  6. Looping macro needed to find intersections of rows and columns
    By Clifford Middleton in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2006, 09:10 AM
  7. Looping through Columns then Rows
    By JCP in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2005, 12:27 AM

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