Loop through dynamic range and return data from two cells in same row

[ch5497]

Hello all,

I've been searching for around an hour and can't seem to find my exact problem. That said, apologies if this has already been answered elsewhere.

As per the guidelines for posting, my excel version is 2010.

I have the following;

Name	Start Time	End Time	Description
37-S01 HHH	06/09/2015 08:00</SPAN> <TBODY> </TBODY>	11/09/2015 18:00</SPAN> <TBODY> </TBODY>	169200</SPAN> <TBODY> </TBODY>	800023</SPAN> <TBODY> </TBODY>
37-S02 FJG	06/09/2015 08:00</SPAN> <TBODY> </TBODY>	25/09/2015 18:00</SPAN> <TBODY> </TBODY>	169200</SPAN> <TBODY> </TBODY>
37-S03 EED	07/09/2015 08:00</SPAN> <TBODY> </TBODY>	27/09/2015 18:00</SPAN> <TBODY> </TBODY>	228810</SPAN> <TBODY> </TBODY>	228815</SPAN> <TBODY> </TBODY>
37-S04 OFP	07/09/2015 08:00</SPAN> <TBODY> </TBODY>	20/09/2015 18:00</SPAN> <TBODY> </TBODY>	810262</SPAN> <TBODY> </TBODY>
37-S05 JMM	07/09/2015 08:00</SPAN> <TBODY> </TBODY>	12/09/2015 18:00</SPAN> <TBODY> </TBODY>	227891</SPAN> <TBODY> </TBODY>	227972</SPAN> <TBODY> </TBODY>
37-S06 TIG	07/09/2015 08:00</SPAN> <TBODY> </TBODY>	18/09/2015 18:00</SPAN> <TBODY> </TBODY>	802186</SPAN> <TBODY> </TBODY>	802186</SPAN> <TBODY> </TBODY>	810289 <TBODY> </TBODY>	810290 <TBODY> </TBODY>	810095 <TBODY> </TBODY>
37-S07 QIN	05/09/2015 08:00</SPAN> <TBODY> </TBODY>	02/10/2015 18:00</SPAN> <TBODY> </TBODY>	228495</SPAN> <TBODY> </TBODY>	167004</SPAN> <TBODY> </TBODY>

<TBODY>
</TBODY>

I'd like to take each of the 'Description' numbers, 169200 for example, and loop through them all, returning a list which contains the 'Description', 'Start Time' and 'End Time' field, like below;

169200</SPAN> <TBODY> </TBODY>	06/09/2015 08:00</SPAN> <TBODY> </TBODY>	11/09/2015 18:00</SPAN> <TBODY> </TBODY>
800023</SPAN> <TBODY> </TBODY>	06/09/2015 08:00</SPAN> <TBODY> </TBODY>	11/09/2015 18:00</SPAN> <TBODY> </TBODY>
169200</SPAN> <TBODY> </TBODY>	06/09/2015 08:00</SPAN> <TBODY> </TBODY>	25/09/2015 18:00</SPAN> <TBODY> </TBODY>
228810</SPAN> <TBODY> </TBODY>	07/09/2015 08:00</SPAN> <TBODY> </TBODY>	27/09/2015 18:00</SPAN> <TBODY> </TBODY>
228815</SPAN> <TBODY> </TBODY>	07/09/2015 08:00</SPAN> <TBODY> </TBODY>	27/09/2015 18:00</SPAN> <TBODY> </TBODY>

<TBODY>
</TBODY>

This would carry on until we run out of 'Description' cells, I haven't completed the table here as it's unneccessary. It doesn't matter whether the loop runs horizontal and then vertical or the other way around, just as long as the data is returned in the format above.

To complicate matters further, the number of entries under 'Description' will vary as will the number of entries under 'Name' - essentially the number of rows and columns will be dynamic and I've chosen the example above to try to demonstrate this. Also, there will be duplicate entries under 'Description' which may have different 'Start Time' and 'End Time' as in the example of 169200 above.

I'm pretty much out of my depth here, and I don't really know where to start or indeed if what I'm after is even possible. Any help anyone can offer would be hugely appreciated.

Many thanks in advance for anyone who can shine any light on this.

 

[MickG]

Try this:-
Data Sheet1, Results Sheet2.

[COLOR="Navy"]Sub[/COLOR] MG10Sep54
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] rR [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cC [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
R = Sheets("Sheet1").Range("A1").CurrentRegion
ReDim nR(1 To UBound(R, 1) * UBound(R, 2), 1 To 3)
[COLOR="Navy"]For[/COLOR] rR = 2 To UBound(R, 1) - 1
    [COLOR="Navy"]For[/COLOR] cC = 4 To UBound(R, 2)
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(R(rR, cC)) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            nR(c, 1) = R(rR, cC): nR(c, 2) = R(rR, 2): nR(c, 3) = R(rR, 3)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] cC
[COLOR="Navy"]Next[/COLOR] rR
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A2").Resize(c, 3)
    .Value = nR
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ch5497]

Hi Mick,

Thanks for the really quick reply.

I've just tried to run that, changing only the worksheet names so they're relevant to my workbook.

I got the Run-time error '1004' error, and it points to the following line when I hit debug;

With Sheets("Sheet4").Range("A2").Resize(c, 3)

Kind regards

 

[MickG]

Noting you changed the sheet name. Have you got a Sheet named "Sheet4" ?????
See here for example:-
https://app.box.com/s/1j4k0rgyqfiuoj77m3koq8z6vnpxwfz0

 

[ch5497]

Mick,

Thank you ever so much for taking the time to create this for me. I've no idea why mine didn't work, I did indeed have a worksheet called "Sheet4".

Anyway, I'm now using your spreadsheet as a base to build upon.

Thanks again for helping a stranger in need.