# Find first and last values based on one value

#### Wes

##### Board Regular
First I appologize if this has already been posted.

I need to find the First value and the last value of a range. A value is put into cell C12: number 1 for example and what happens is that excel will go down range H2:H9 and find the First Depth - "0" in this case and the last
Depth 100.
Daily Reporting Ver 1.0.3.xls
EFGHI
21Rigup/TearDown10
37RigService10
42Drilling1100
56Trips2100
615PressureTest2100
74Coring2100
82Drilling2200
97RigService2200
10
11
12BHAValue1
13MinDepth
14MaxDepth
15
DailyReports

All help appreciated.

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

##### MrExcel MVP
Is G2:H9 is always sorted on G (in ascending order) as your exhibit appears to suggest?

#### Wes

##### Board Regular
For the most part. On the rare occasion the depth will become less and then increase.

##### MrExcel MVP
see the example:
Book1
ABCD
1KeyValue
210
3110
410
52500
625
722
8310
921
10315
11
12MaxMin
131100
1425001
1531510
Sheet1

the formulas are of the form:

=MAX(IF(\$A\$2:\$A\$10=A13,\$B\$2:\$B\$10))

& need to be array enterred using control + shift + enter, not just enter

#### Wes

##### Board Regular
Thanks PaddyD, that works great for finding min and max ray: I'm off to the races

##### MrExcel MVP
you're welcome. fwiw, this can also be done using a pivot table - an easier option in my view.

#### Wes

##### Board Regular
I'd actually like to do it in VBA any ideas?

#### fwguam

##### New Member
"need to be array enterred using control + shift + enter" What is array enter?

Thanks,
fwguam

##### MrExcel MVP
wes,

describe what you want the vba to do in a little more detail.

fwguam - welcome to the board! review the help file for "About array formulas and how to enter them" - post back if it's still not clear.

#### Wes

##### Board Regular
I would like the VBA code to find the last value and the first value of a range on a spreadsheet that is determine by one value. The value would be where the BHA# 1 started and where the BHA #1 ended. These values would also be found for BHA #2. Which we have done on the spreadsheet and I'm just repeting myself because I need the typing skills ... really. :wink:

Now, a userform would be set up with a text box, for the BHA number (txBhaNum) and text boxes ( txStart & txEnd) for the start and end depths. Thie BHA number/value would be controlled by the user. As the user changed the BHA value the first and the last depth values would be found and displayed in threir own text boxes on the userform.

So what is needed is code to place in the userform that refrences a range in a spreadsheet (DailyReports) and shows the values of end depth and start depth ( txStart & txEnd) on the userform all controlled by the BHA number (txBhaNum) on the userform.

I hope this helps a bit.

Replies
4
Views
457
Replies
4
Views
356
Replies
0
Views
189
Replies
7
Views
279
Replies
0
Views
92

1,171,043
Messages
5,873,444
Members
432,980
Latest member
KMorrison12345

### 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.

### Which adblocker are you using?

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

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