Find if based on two criteria with offset function

L

Lizard07

Board Regular
Joined
Jul 20, 2011
Messages
103
Hello - I have a somewhat complicated request. I have a report that is about 500 sheets I run each week for 15 facilities. I need a macro to go through each sheet and if it finds "Stop Time" in column H and then finds "Delay" immediately above it, I would like it to return the value in cell B11 onto the "Master" sheet. Below is what the report looks like, the bold text indicates what I want the macro to locate.

<TABLE style="WIDTH: 366pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=486 x:str><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 239pt; mso-width-source: userset; mso-width-alt: 11629" width=318><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>28</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Clock In</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl23 height=17 width=90> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl23 width=78> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Trip Begin</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>22</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Inspection:Pre-Trip</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Hook:R50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num="0"> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Stop Time</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num>109.2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>115</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Motion Segment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>DELAY</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num="0"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Stop Time</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num>61.4</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>76</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Motion Segment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>67</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Deliver:PERISHABLE Bill:8594130100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num="0"> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Stop Time</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num>8.7</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>19</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Motion Segment</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num>44</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Deliver:PERISHABLE Bill:8593730100</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl25 height=17 width=90 x:num="0">.</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl24 width=78 x:num="0"> </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 239pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" dir=ltr class=xl22 width=318>Stop Time</TD></TR></TBODY></TABLE>


Is this possible? If not, could I have hte macro tag that sheet in someway if it meets the above criteria so I know to refer to it?

Thanks
 
Sort by date Sort by votes
I'm currently using cell value is equal to and then the formula you provided with the correct column number...am I missing something?
Click to expand...
Yes, you need to use the "Formula Is" option, not the "Cell Value" option.<!-- / message -->
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
P
  • Question Question
Select the time from the list and search with Selenium VBA
Replies
6
Views
614
John_w
J
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,613
Messages
6,179,898
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top