How can I find a cell with a certain content and then read a range relatively to this cell

Just as an example, the following snippet uses the Find method to find a value
in a range, and then uses offset on the cell the value is found in to do some
comparisons:-

Set SearchMe = Sht2.Range("A1:A" & LRowSht2)
y = 1

For r = LRowSht1 To 1 Step -1
Set findme = Sht1.Cells(r, 1)
With SearchMe
Set found = .Find(what:=findme, LookIn:=xlValues)
If Not found Is Nothing Then
fnd = 0
For z = 1 To 4
If found.Offset(0, z).Value < _
findme.Offset(0, z).Value Then
fnd = fnd + 1
End If
Next z

If fnd 0 Then

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Pascal" wrote in message
...
How can I find a cell with a certain content and then read a range relatively
to this cell


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004

Hi Ke

lets say somewhere in colom
there is a cell with value "BOD
and I want to read the data range starting below this cell until "EOD
how would I prosee

regards Pasca

----- Ken Wright wrote: ----

Just as an example, the following snippet uses the Find method to find a valu
in a range, and then uses offset on the cell the value is found in to do som
comparisons:

Set SearchMe = Sht2.Range("A1:A" & LRowSht2
y =

For r = LRowSht1 To 1 Step -
Set findme = Sht1.Cells(r, 1
With SearchM
Set found = .Find(what:=findme, LookIn:=xlValues
If Not found Is Nothing The
fnd =
For z = 1 To
If found.Offset(0, z).Value <
findme.Offset(0, z).Value The
fnd = fnd +
End I
Next

If fnd 0 The

--
Regard
Ken....................... Microsoft MVP - Exce
Sys Spec - Win XP Pro / XL 00/02/0

---------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-
---------------------------------------------------------------------------



"Pascal" wrote in messag
..
How can I find a cell with a certain content and then read a range relativel
to this cel


--
Outgoing mail is certified Virus Free
Checked by AVG anti-virus system (http://www.grisoft.com)
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/200

If you know they will be there

Dim rng as Range, rng1 as Range
set rng = Columns(1).Find(what:="BOD", LookIn:=xlValues)
set rng1 = Columns(1).Find(what:="EOD", LookIn:=xlValues)
if not rng is nothing and not rng1 is nothing then
for each cell in Range(rng.Offset(1,0),rng1.offset(-1,0))

Next
End If

--
Regards,
Tom Ogilvy


"Pascal" wrote in message
...
Hi Ken

lets say somewhere in colom A
there is a cell with value "BOD"
and I want to read the data range starting below this cell until "EOD"
how would I proseed

regards Pascal

----- Ken Wright wrote: -----

Just as an example, the following snippet uses the Find method to
find a value
in a range, and then uses offset on the cell the value is found in to
do some
comparisons:-

Set SearchMe = Sht2.Range("A1:A" & LRowSht2)
y = 1

For r = LRowSht1 To 1 Step -1
Set findme = Sht1.Cells(r, 1)
With SearchMe
Set found = .Find(what:=findme, LookIn:=xlValues)
If Not found Is Nothing Then
fnd = 0
For z = 1 To 4
If found.Offset(0, z).Value < _
findme.Offset(0, z).Value Then
fnd = fnd + 1
End If
Next z

If fnd 0 Then

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

---------------------------------------------------------------------
-------
It's easier to beg forgiveness than ask permission :-)
---------------------------------------------------------------------
-------



"Pascal" wrote in message
...
How can I find a cell with a certain content and then read a range
relatively
to this cell


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.608 / Virus Database: 388 - Release Date: 03/03/2004