# looping within a a subset of data within a range?

1. ## looping within a a subset of data within a range?

Hi,

Hopefully this will make sense! I'm looking for some vba code that look at all rows of data in a sheet, but loop within groups of that data.

For example in the below, I have some data with a value of 0 in ColumnC and what I want to do is to copy the value in the cell above to replace the 0, but that I want that to only happen within each particular group. E.g. I don't want the first record (record 10) in group 4 to be copied from record 09 of group 3 as it crosses groups.

ColumnA ColumnB ColumnC
Record 01 group 1 124
Record 02 group 1 0
Record 03 group 1 432
Record 04 group 2 123
Record 05 group 2 753
Record 06 group 2 0
Record 07 group 2 938
Record 08 group 3 563
Record 09 group 3 324
Record 10 group 4 0
Record 11 group 4 234

Any help would be great!

Thanks,

2. ## Re: looping within a a subset of data within a range?

=if(and(c2=0,b1=b2),c1,c2)

3. ## Re: looping within a a subset of data within a range?

Hi,

That's given me a start, thanks. I'm not sure it's giving me exactly what I need though as it's using specific cell values, but it's doing what I want manually.

I need to loop it through the whole worksheet range with a macro though, I'm not really sure what VBA code would be required to adapt the formula?

4. ## Re: looping within a a subset of data within a range?

Hi groexcel
Try this attachment

5. ## Re: looping within a a subset of data within a range?

FWIW:

``Please Login or Register  to view this content.``

6. ## Re: looping within a a subset of data within a range?

thanks everyone, on the basis of the first reply I worked on the formula, so this is doing what i want:

=IF(AND(A2="",A1="",C1=C2),S2+Z1,IF(AND(A2<>"",A1="",C1=C2),S2+Z1,S2))

I'm looking for something similar to YasserKhalil & John's output, but I'm not sure how to transpose my formula into that method. I wondered if you could help with that final step please?

7. ## Re: looping within a a subset of data within a range?

Maybe:

Can you attach a sample file? Show which column would contain your formula, and provide values for Columns S and Z.

8. ## Re: looping within a a subset of data within a range?

Hi,

Sure, thanks for taking a look. I've attached some sample data. To be honest most of the columns are irrelevant, so that's why there's nothing in most of them! I've pasted my working formula into column Z, but that's what I want to automate rather than manually copying a formula in and dragging it down. As a overview, if columnA is empty then I want to consolidate the values in columnS down to the 'next' columnA row containing a value (but only within the group listed in column). If columnA has a value and columnS has a value then it can stay unchanged. Hopefully that explains what I'm looking to do, columnZ has the correct data.

9. ## Re: looping within a a subset of data within a range?

Try:

``Please Login or Register  to view this content.``

10. ## Re: looping within a a subset of data within a range?

Hi John,

That's great, thanks! I've updated my code and it checks out fine. I know i'm definitely pushing my luck now, but I've discovered I need to also perform a sort of running total to re-sequence columnD values when I remove the 'blank' rows in ColumnA. I need to update column D with values, 1, 2, 3, 4, 5, etc. resetting on each group after I've removed the blank lines. For example, if I remove one of those empty tray lines which has a columnD value of 1, the next record's columnD value might be 2 or 3, but I need it to start at 1 again and increment until it hits the next group.

I appreciate you've been really helpful, so no problems if you're too busy.

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