can anybody help me

P

Pascal

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

Ken Wright

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 said:
How can I find a cell with a certain content and then read a range relatively
Click to expand...
to this cell
 
P

Pascal

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 said:
How can I find a cell with a certain content and then read a range relativel
Click to expand...
to this cel
 
T

Tom Ogilvy

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
 
Top