Find first and last values based on one value

[Wes]

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
	E	F	G	H	I
1	Code	Activity	BHA	Depth
2	1	Rigup/TearDown	1	0
3	7	RigService	1	0
4	2	Drilling	1	100
5	6	Trips	2	100
6	15	PressureTest	2	100
7	4	Coring	2	100
8	2	Drilling	2	200
9	7	RigService	2	200
10
11
12		BHAValue	1
13		MinDepth
14		MaxDepth
15
DailyReports

All help appreciated.

 

[Aladin Akyurek]

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

 

[Wes]

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

 

[PaddyD]

see the example:

Book1
	A	B	C	D
1	Key	Value
2	1	0
3	1	10
4	1	0
5	2	500
6	2	5
7	2	2
8	3	10
9	2	1
10	3	15
11
12		Max	Min
13	1	10	0
14	2	500	1
15	3	15	10
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]

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

 

[PaddyD]

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

 

[Wes]

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

 

[fwguam]

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

Thanks,
fwguam

 

[PaddyD]

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]

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.