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.

 

[acw]

Les

I've created a userform called userform1 which has a textbox (txBhaNum), 2 labels (label1, label2) and a commandbutton.

The main macro loads the form
Sub main1()
UserForm1.Show

End Sub

The textbox has the following in the change area
Private Sub txBhaNum_Change()
Label1.Caption = Evaluate("=Max(--(a1:a10 = " & txBhaNum & ") * b1:b10 )")
Label2.Caption = Evaluate("=Min(if(a1:a10 = " & txBhaNum & ", b1:b10 ))")

End Sub
The commandbutton has the following code
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

The data on the spreadsheet was in the range A1:B9

Whenever the data was changed in the textbox, the labels reflected the results. Clicking the button closed the form.

HTH

Tony

 

[Wes]

Tony, works perfect ray: just what the Doctor ordered.

Thanks

Wes

 

[rohini2883]

can this be extent to collumn "c" as well?

 

[rohini2883]

can i get the same for the column c