# Looping through rows then columns

1. ## 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. ## 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. ## 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. ## Re: Looping through rows then columns

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

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

#### 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