Dave: Thanks for pointing this out.

Dan:
Sorry about this omission. The variable cl would represent the range
that contains the data you were looking for. As Dave suggested, you
would want something like this:

If Not(IsNothing(cl)) Then ''value was found; cl contains the reference
to this range
''do the copying stuff
End If

This roughly translates into: if "variable" = "found in range" then do
stuff

Since you are looking for two values, you will have to create a second
variable and your if statement would look like this:

If Not(IsNothing(cl)) And Not(IsNothing(cl2)) Then
''do the copying stuff
End If

As for the 'do the copying stuff', I'm not sure if I understand what
you need. Let me rephrase your requirement: you have tow ranges that
you each want to search for a value. If any cell within the respective
range has that target value, then you want the intersection of this
cell's row with column P copied to some cell in the next sheet. Did I
interpret this correctly? cl.Row would then be the row index of your
target cell. Appended to "P", it represents your target cell's address
(that's the part where it said 'myWorksheet.Range("P" & cl.Row)')

Let me know if I can be of further assistance.

Regards,
Steve

Dave Peterson wrote:
You have to tell what rng is before you use it:

dim rng as range
dim cl as range
with activesheet
set rng = .range("a1:X999") 'whatever???
end with

set cl = rng.find("somevalue")

if cl is nothing then
'not found
else
'was found
end if



wrote:

Hi Steve

when i try to run the first part of the code:
=====================================
Dim rng As Excel.Range, cl As Excel.Range

Set cl = rng.Find("someValue")
=====================================
i get the following error:
Runtime error '91'
Object variable or with block variable not set.

am i supposed to substitute something in the code?

can you also please give me some more pointers on finding the row with
cl.row

i don't actually know what you mean with that.

thanks

dan

wrote:
You can use the find method in its simplest form as follows:

Dim rng As Excel.Range, cl As Excel.Range

Set cl = rng.Find("someValue")

Then you can find out the row index with cl.Row. Then you can find the
intersection with column P like so:

Dim intersect As Range

Set intersect = myWorksheet.Range("P" & cl.Row)

Regards,
Steve

wrote:

hi bob

i have seen your post to study the find command, and believe me i have.


i have also trawled the web for help with the find method, but I am
still hitting a brick wall!! my knowledge of VBA is obviously not
sufficient to do this and that is why i am posting my requests here to
ask for assistance from the guys!!!

i apologize for the inconvenience!

regards,

dan

Bob Phillips wrote:
Look at Find in VBA help as I previously mentioned.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hi

if you specify for an "If/Then" or a "Select Case", to find a value =
to a range, will it actually search the whole range, or does that bomb
out?

I want to create a statement similar to the following:

if "variable" = "found in range" and "variable2" = "found in range2"
then "copy the intersect of row with variable and variable2 in and
column p" to next sheet.

I know this is all wrong in terms of syntax, but i don't have the
necessary knowledge to write the statement. I can do the basics, but
this is beyond what I can do!!

any help will be appreciated.

thanks,

dan


--

Dave Peterson