Unable to get Match property of worksheetfunction
 

I searched google groups for this, but all of the questions i found had to do
with using match with the 3rd argument being 0. Therefore if a match is not
found, it returns an error. However, I was trying the match function without
the 3rd argument. I was trying, and doing it successfully from the immediate
window to find where in my range to insert some rows. However, once the code
is running, neither worksheetfunction.match nor application.match works. I
did try, while still in debug mode, to do a match wtih a value that would be
found. This did return a correct value.

What I am trying to do:
iMatchStore = worksheetfunction.match(sStaffStore, rngCEIWork)
and then later
rngCEIWork.Range("a1").offset(iMatchStore).resize( iStoreDiff) _
.entirerow.insert shift:=xldown

I was unable to find any discussions on google groups that specifically
addressed the issue of when an exact match was not being sought. Perhaps it
was because I couldn't think of how to phrase it. But, given these
circumstances, does anyone know of a work around. Of course I could just
loop through the range and find it myself, but would prefer it if I could do
it this or a similar way.

Thanks.

--
Kevin Vaughn

Unable to get Match property of worksheetfunction
 

There should be no problem except you might not have a full understanding of
Match. If you don't specify a value it assumes:

MATCH(lookup_value,lookup_array,match_type)
"If match_type is omitted, it is assumed to be 1"

When match_type is equal to 1 or omitted, then data is assumed to be sorted
ascending:

"If match_type is 1, MATCH finds the largest value that is less than or
equal to lookup_value. Lookup_array must be placed in ascending order:
....-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE."

If your first value in the lookup range is larger than the lookup value, you
could get an error unless there is an exact match.

I think your main problem is data and data orientation.

--
Regards,
Tom Ogilvy


"Kevin Vaughn" wrote in message
...
I searched google groups for this, but all of the questions i found had to
do
with using match with the 3rd argument being 0. Therefore if a match is
not
found, it returns an error. However, I was trying the match function
without
the 3rd argument. I was trying, and doing it successfully from the
immediate
window to find where in my range to insert some rows. However, once the
code
is running, neither worksheetfunction.match nor application.match works.
I
did try, while still in debug mode, to do a match wtih a value that would
be
found. This did return a correct value.

What I am trying to do:
iMatchStore = worksheetfunction.match(sStaffStore, rngCEIWork)
and then later
rngCEIWork.Range("a1").offset(iMatchStore).resize( iStoreDiff) _
.entirerow.insert shift:=xldown

I was unable to find any discussions on google groups that specifically
addressed the issue of when an exact match was not being sought. Perhaps
it
was because I couldn't think of how to phrase it. But, given these
circumstances, does anyone know of a work around. Of course I could just
loop through the range and find it myself, but would prefer it if I could
do
it this or a similar way.

Thanks.

--
Kevin Vaughn

Unable to get Match property of worksheetfunction
 

Ok, I see what is happening now. When I was testing in the immediate window,
I was using a value that didn't exist in my range, but it fell between values
so there wasn't an error. The first value is less than the value of the
first cell so it is returning #N/A. So that means I can't use this method
(or at least until my value is greater than the first value in my range
subject to testing.)

Thanks.
--
Kevin Vaughn


"Tom Ogilvy" wrote:

There should be no problem except you might not have a full understanding of
Match. If you don't specify a value it assumes:

MATCH(lookup_value,lookup_array,match_type)
"If match_type is omitted, it is assumed to be 1"

When match_type is equal to 1 or omitted, then data is assumed to be sorted
ascending:

"If match_type is 1, MATCH finds the largest value that is less than or
equal to lookup_value. Lookup_array must be placed in ascending order:
....-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE."

If your first value in the lookup range is larger than the lookup value, you
could get an error unless there is an exact match.

I think your main problem is data and data orientation.

--
Regards,
Tom Ogilvy


"Kevin Vaughn" wrote in message
...
I searched google groups for this, but all of the questions i found had to
do
with using match with the 3rd argument being 0. Therefore if a match is
not
found, it returns an error. However, I was trying the match function
without
the 3rd argument. I was trying, and doing it successfully from the
immediate
window to find where in my range to insert some rows. However, once the
code
is running, neither worksheetfunction.match nor application.match works.
I
did try, while still in debug mode, to do a match wtih a value that would
be
found. This did return a correct value.

What I am trying to do:
iMatchStore = worksheetfunction.match(sStaffStore, rngCEIWork)
and then later
rngCEIWork.Range("a1").offset(iMatchStore).resize( iStoreDiff) _
.entirerow.insert shift:=xldown

I was unable to find any discussions on google groups that specifically
addressed the issue of when an exact match was not being sought. Perhaps
it
was because I couldn't think of how to phrase it. But, given these
circumstances, does anyone know of a work around. Of course I could just
loop through the range and find it myself, but would prefer it if I could
do
it this or a similar way.

Thanks.

--
Kevin Vaughn

Unable to get Match property of worksheetfunction
 

You can still use it.

You could test first if the value is lower than the lowest value and act
according, but if it isn't then use match.

Dim res as Variant
for each cell in Range("A1:A10")
if cell.value < Range("M1") then
' lower than
else
res = Application.Match(cell.Value, Range("M1:M200"),1)
set rng = Range("M1:M200")(res)
rng.select
end if
Next


Or you could just use Match and trap for the value.

Dim res as Variant
for each cell in Range("A1:A10")
res = Application.Match(cell.Value, Range("M1:M200"),1)
if iserror(res) then
' match raised an error
else
set rng = Range("M1:M200")(res)
rng.select
end if
Next

--
Regards,
Tom Ogilvy

"Kevin Vaughn" wrote in message
...
Ok, I see what is happening now. When I was testing in the immediate
window,
I was using a value that didn't exist in my range, but it fell between
values
so there wasn't an error. The first value is less than the value of the
first cell so it is returning #N/A. So that means I can't use this method
(or at least until my value is greater than the first value in my range
subject to testing.)

Thanks.
--
Kevin Vaughn


"Tom Ogilvy" wrote:

There should be no problem except you might not have a full
understanding of
Match. If you don't specify a value it assumes:

MATCH(lookup_value,lookup_array,match_type)
"If match_type is omitted, it is assumed to be 1"

When match_type is equal to 1 or omitted, then data is assumed to be
sorted
ascending:

"If match_type is 1, MATCH finds the largest value that is less than or
equal to lookup_value. Lookup_array must be placed in ascending order:
....-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE."

If your first value in the lookup range is larger than the lookup value,
you
could get an error unless there is an exact match.

I think your main problem is data and data orientation.

--
Regards,
Tom Ogilvy


"Kevin Vaughn" wrote in message
...
I searched google groups for this, but all of the questions i found
had to
do
with using match with the 3rd argument being 0. Therefore if a match
is
not
found, it returns an error. However, I was trying the match function
without
the 3rd argument. I was trying, and doing it successfully from the
immediate
window to find where in my range to insert some rows. However, once
the
code
is running, neither worksheetfunction.match nor application.match
works.
I
did try, while still in debug mode, to do a match wtih a value that
would
be
found. This did return a correct value.

What I am trying to do:
iMatchStore = worksheetfunction.match(sStaffStore, rngCEIWork)
and then later
rngCEIWork.Range("a1").offset(iMatchStore).resize( iStoreDiff) _
.entirerow.insert shift:=xldown

I was unable to find any discussions on google groups that
specifically
addressed the issue of when an exact match was not being sought.
Perhaps
it
was because I couldn't think of how to phrase it. But, given these
circumstances, does anyone know of a work around. Of course I could
just
loop through the range and find it myself, but would prefer it if I
could
do
it this or a similar way.

Thanks.

--
Kevin Vaughn

Unable to get Match property of worksheetfunction
 

set rng = Range("M1:M200")(res)

I wasn't familiar with the use of (res) as above. Interesting.

Thanks for the ideas.
--
Kevin Vaughn


"Tom Ogilvy" wrote:

You can still use it.

You could test first if the value is lower than the lowest value and act
according, but if it isn't then use match.

Dim res as Variant
for each cell in Range("A1:A10")
if cell.value < Range("M1") then
' lower than
else
res = Application.Match(cell.Value, Range("M1:M200"),1)
set rng = Range("M1:M200")(res)
rng.select
end if
Next


Or you could just use Match and trap for the value.

Dim res as Variant
for each cell in Range("A1:A10")
res = Application.Match(cell.Value, Range("M1:M200"),1)
if iserror(res) then
' match raised an error
else
set rng = Range("M1:M200")(res)
rng.select
end if
Next

--
Regards,
Tom Ogilvy