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

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

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

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

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

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