Finding data automatically in a wide range of information

[MarcSwift]

Hi All,

I'm trying to make a project and getting a little confused.

I basically need to use VBA to automatically sort out information which is copied from access and pasted into a spreadsheet in Excel. I want to figure out one bit before i move any further.

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 22.5pt" height=30><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 22.5pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=30 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>15:43</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>GORSEINON</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 25.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=34 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:03</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>SWANSEA</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 25.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=34 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:04</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>CWMBRAN</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=17 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:04</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>TIGER BAY</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 22.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=30 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:04</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PATCHWAY</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 22.5pt" height=30><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 22.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=30 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:04</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>ST AUSTELL</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 25.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=34 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:05</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>CARDIFF</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 25.5pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=34 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:05</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>MERTHYR</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=17 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:05</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>NEWPORT</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl71 height=17 width=64>02-Jun-11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>asmith</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl73 width=64>16:05</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PC</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl72 width=64>PLYMOUTH</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl74 width=64>4440806</TD></TR></TBODY></TABLE>

As you can see above, there is columns with information. This is only a tiny segment from the whole list on the spreadsheet.

This segment relates to a colleagues work time. He should of had his break at 15:45 and come back to work at 16:00. But he stopped working at 15:43 and started at 16:03. At the moment i find this manually.

I need to use VBA to detect this information automatically by either a macro or in modules. The problem im having is that the information varies in each cell. For example, the above break times may appear in A3 and B3, but the next colleague might have that information in F3 and G3. Quite confusing.

Any help would be MUCH appreciated! Thanks a lot guys!!

Regards.

 

[jreedich]

I guess I have more questions than answers for you.
1:Where is this information coming from?
2:Does a list of break times exist someplace in the workbook?
3:Are all employees on the same schedule for breaks, lunc etc.?

These are a few to start with.

 

[MarcSwift]

Hello! Thanks for the reply!!

1.The information is copied from access and pasted into a blank worksheet in excel

2. There is no break list stored anywhere in the workbook. I guess i have to store them somewhere?

3. The colleagues break times differ depending on what shift they are on, but i will try and create different worksheets with vba to match each shift.

I just need to crack this one problem, then i guess i can alter it to match the other break times.

Thanks again.

Regards,