Hey all,
I've been scratching my head for the past 3 days looking for an answer online, so far I've been unsuccessful...
My issue is the following: I have several small tables one after the other (separated by a graph each time) I need to loop through column N (first table header starts at N25), looking for entries that fall between "5:30 AM" and "11:00 PM". When found, I have to select the entire range of the values next to column N (in column O) and copy the ranges in a new sheet. It would be easier if the amounts of rows were the same but they fluctuate by 1 or 2 rows... example of a table below.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]time (col n)[/TD]
[TD="align: center"]amount (col o)[/TD]
[/TR]
[TR]
[TD="align: center"]5:00 AM[/TD]
[TD="align: center"]1542[/TD]
[/TR]
[TR]
[TD="align: center"]5:30 AM[/TD]
[TD="align: center"]2345[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:00 PM[/TD]
[TD="align: center"]4454[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]time (col n)[/TD]
[TD="align: center"]amount (col o)[/TD]
[/TR]
[TR]
[TD="align: center"]5:30 AM[/TD]
[TD="align: center"]1541[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:00 PM[/TD]
[TD="align: center"]4242[/TD]
[/TR]
[TR]
[TD="align: center"]11:30 PM[/TD]
[TD="align: center"]5356[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]time (col n)[/TD]
[TD="align: center"]amount (col o)[/TD]
[/TR]
[TR]
[TD="align: center"]4:30 AM[/TD]
[TD="align: center"]7445[/TD]
[/TR]
[TR]
[TD="align: center"]5:00 AM[/TD]
[TD="align: center"]4521[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:30 AM[/TD]
[TD="align: center"]4547[/TD]
[/TR]
</tbody>[/TABLE]
So, if I were to do it manually, which is how I'm doing it right now, I would do it like this:
The end result in sheet2 would look like this, more or less:
[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD="align: center"]Time[/TD]
[TD="align: center"]1st table[/TD]
[TD="align: center"]2nd table[/TD]
[TD="align: center"]3rd table[/TD]
[/TR]
[TR]
[TD="align: center"]5:30 AM[/TD]
[TD="align: center"]1542[/TD]
[TD="align: center"]1541[/TD]
[TD="align: center"]7445[/TD]
[/TR]
[TR]
[TD="align: center"]6:00 AM[/TD]
[TD="align: center"]2425[/TD]
[TD="align: center"]3254[/TD]
[TD="align: center"]6242[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:30 AM[/TD]
[TD="align: center"]4454[/TD]
[TD="align: center"]5356[/TD]
[TD="align: center"]4547[/TD]
[/TR]
</tbody>[/TABLE]
I've been slowly but surely automating my reporting but this one is out of my range of knowledge in VBA..
</graph></graph>
I've been scratching my head for the past 3 days looking for an answer online, so far I've been unsuccessful...
My issue is the following: I have several small tables one after the other (separated by a graph each time) I need to loop through column N (first table header starts at N25), looking for entries that fall between "5:30 AM" and "11:00 PM". When found, I have to select the entire range of the values next to column N (in column O) and copy the ranges in a new sheet. It would be easier if the amounts of rows were the same but they fluctuate by 1 or 2 rows... example of a table below.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]time (col n)[/TD]
[TD="align: center"]amount (col o)[/TD]
[/TR]
[TR]
[TD="align: center"]5:00 AM[/TD]
[TD="align: center"]1542[/TD]
[/TR]
[TR]
[TD="align: center"]5:30 AM[/TD]
[TD="align: center"]2345[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:00 PM[/TD]
[TD="align: center"]4454[/TD]
[/TR]
</tbody>[/TABLE]
<graph here="">GRAPH HERE
</graph>
<graph here=""> </graph>
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]time (col n)[/TD]
[TD="align: center"]amount (col o)[/TD]
[/TR]
[TR]
[TD="align: center"]5:30 AM[/TD]
[TD="align: center"]1541[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:00 PM[/TD]
[TD="align: center"]4242[/TD]
[/TR]
[TR]
[TD="align: center"]11:30 PM[/TD]
[TD="align: center"]5356[/TD]
[/TR]
</tbody>[/TABLE]
<graph here="">GRAPH HERE
</graph>
<graph here=""> </graph>
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]time (col n)[/TD]
[TD="align: center"]amount (col o)[/TD]
[/TR]
[TR]
[TD="align: center"]4:30 AM[/TD]
[TD="align: center"]7445[/TD]
[/TR]
[TR]
[TD="align: center"]5:00 AM[/TD]
[TD="align: center"]4521[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:30 AM[/TD]
[TD="align: center"]4547[/TD]
[/TR]
</tbody>[/TABLE]
So, if I were to do it manually, which is how I'm doing it right now, I would do it like this:
- Select values (col O) that match the time between 5:30 AM and 11:00 PM
- Copy values & paste them in sheet2 (first range goes in sheet2!B1:B, second range sheet2!C1:C, and so on)
- Repeat until done, usually 30 times (since it's usually 1 table per day.)
The end result in sheet2 would look like this, more or less:
[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD="align: center"]Time[/TD]
[TD="align: center"]1st table[/TD]
[TD="align: center"]2nd table[/TD]
[TD="align: center"]3rd table[/TD]
[/TR]
[TR]
[TD="align: center"]5:30 AM[/TD]
[TD="align: center"]1542[/TD]
[TD="align: center"]1541[/TD]
[TD="align: center"]7445[/TD]
[/TR]
[TR]
[TD="align: center"]6:00 AM[/TD]
[TD="align: center"]2425[/TD]
[TD="align: center"]3254[/TD]
[TD="align: center"]6242[/TD]
[/TR]
[TR]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]11:30 AM[/TD]
[TD="align: center"]4454[/TD]
[TD="align: center"]5356[/TD]
[TD="align: center"]4547[/TD]
[/TR]
</tbody>[/TABLE]
I've been slowly but surely automating my reporting but this one is out of my range of knowledge in VBA..
</graph></graph>