Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found in the range.

However, i now only want to return the row number where column A in that row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

Obviously the simplest is to loop through the data and stop when the
conditions are satisfied. You can minimized the cells looked at with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient approach might be
warranted. For example the first blank row marks the end of filled cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found in the
range.

However, i now only want to return the row number where column A in that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

Hi Tom

thanks for your response

the situation is that i'm creating an excel "database" for a manufacturing
company who are shipping the goods overseas in containers, and what they
want to do is track which goods end up in each container (5 items per
container) ... initially i was told that the container number was unique so
therefore the match function worked fine, however, now i'm told that the
container number isn't unique, but the container number in combination with
a seal number is. However, the seal number is only known basically just
before the containers are put on the ship.

the process that i'm coding at the moment checks that the items have passed
their final inspection and are able to be packed. Then in the same screen
i'm asking the user to nominate the proposed container for these items to go
into (this container might change later) ...when they enter a container
number i want to see if the container number exists in my list where there
is no seal number (which means that the container hasn't been shipped) and
if it is there to continue and if it isn't to prompt the user to enter it.

i'm looking at 15000 items so therefore 3000 containers so was hoping for a
'better' solution then looping through the records. It is possible to sort
the container sheet if that would make the process quicker.

Hopefully i've explained it adequately, does this change the answer?

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
Obviously the simplest is to loop through the data and stop when the
conditions are satisfied. You can minimized the cells looked at with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient approach might be
warranted. For example the first blank row marks the end of filled cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found in the
range.

However, i now only want to return the row number where column A in that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

You can use an array formula:

res = Evaluate("Match(""" & containername &
""",If(A1:A20000="""","""",B1:B20000),0)")
? res
4484

You can test the results of res with

if iserror(res) then
msgbox "No unshipped container found with name " & containername
else
msgbox "found at row " & res
End if



--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
Hi Tom

thanks for your response

the situation is that i'm creating an excel "database" for a manufacturing
company who are shipping the goods overseas in containers, and what they
want to do is track which goods end up in each container (5 items per
container) ... initially i was told that the container number was unique
so
therefore the match function worked fine, however, now i'm told that the
container number isn't unique, but the container number in combination
with
a seal number is. However, the seal number is only known basically just
before the containers are put on the ship.

the process that i'm coding at the moment checks that the items have
passed
their final inspection and are able to be packed. Then in the same screen
i'm asking the user to nominate the proposed container for these items to
go
into (this container might change later) ...when they enter a container
number i want to see if the container number exists in my list where there
is no seal number (which means that the container hasn't been shipped) and
if it is there to continue and if it isn't to prompt the user to enter it.

i'm looking at 15000 items so therefore 3000 containers so was hoping for
a
'better' solution then looping through the records. It is possible to
sort
the container sheet if that would make the process quicker.

Hopefully i've explained it adequately, does this change the answer?

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
Obviously the simplest is to loop through the data and stop when the
conditions are satisfied. You can minimized the cells looked at with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient approach might be
warranted. For example the first blank row marks the end of filled
cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found in the
range.

However, i now only want to return the row number where column A in
that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

Hi Tom

thanks for the speedy response ... i've used the formula you gave me but i
keep getting an error 2042

Sheets("LookupContainer").Select 'only put this in to see if it made
a difference
res = Evaluate("Match(""" & contnumber &
""",If(A1:A20000="""","""",B1:B20000),0)")
Sheets("LookupContainer").Range("C" & res & "").Value =
UserForm3.txtSealNo.Value

what have i done wrong?
additionally, can i use range names instead of A1:A20000 & B1:B20000 and if
so, how

Cheers
JulieD


"Tom Ogilvy" wrote in message
...
You can use an array formula:

res = Evaluate("Match(""" & containername &
""",If(A1:A20000="""","""",B1:B20000),0)")
? res
4484

You can test the results of res with

if iserror(res) then
msgbox "No unshipped container found with name " & containername
else
msgbox "found at row " & res
End if



--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
Hi Tom

thanks for your response

the situation is that i'm creating an excel "database" for a
manufacturing
company who are shipping the goods overseas in containers, and what they
want to do is track which goods end up in each container (5 items per
container) ... initially i was told that the container number was unique
so
therefore the match function worked fine, however, now i'm told that the
container number isn't unique, but the container number in combination
with
a seal number is. However, the seal number is only known basically just
before the containers are put on the ship.

the process that i'm coding at the moment checks that the items have
passed
their final inspection and are able to be packed. Then in the same
screen
i'm asking the user to nominate the proposed container for these items
to
go
into (this container might change later) ...when they enter a container
number i want to see if the container number exists in my list where
there
is no seal number (which means that the container hasn't been shipped)
and
if it is there to continue and if it isn't to prompt the user to enter
it.

i'm looking at 15000 items so therefore 3000 containers so was hoping
for
a
'better' solution then looping through the records. It is possible to
sort
the container sheet if that would make the process quicker.

Hopefully i've explained it adequately, does this change the answer?

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
Obviously the simplest is to loop through the data and stop when the
conditions are satisfied. You can minimized the cells looked at with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient approach might
be
warranted. For example the first blank row marks the end of filled
cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found in
the
range.

However, i now only want to return the row number where column A in
that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

Unless you qualify the ranges with sheet name, the sheet would need to be
active - same as using the formula in a worksheet. You can use a named
range. I used the variable contnumber, but in your original code, you used
contname. Assume the named range RNGA refers to column A in the data sheet
and RNGB refers to column B (appropriate rows).
res = Evaluate("Match(""" & contName & _
""",If(RNGA="""","""",RNGB),0)")
if not iserror(res) then
Sheets("LookupContainer").Range("C" & res & "").Value = _
UserForm3.txtSealNo.Value
else
msgbox "Not found"
End if

Anyway, it works fine for me.
As written it supplies a string to the first argument of match. If you are
actually looking for a number, then you need to remove the quotes and pass
in a number.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
Hi Tom

thanks for the speedy response ... i've used the formula you gave me but i
keep getting an error 2042

Sheets("LookupContainer").Select 'only put this in to see if it
made
a difference
res = Evaluate("Match(""" & contnumber &
""",If(A1:A20000="""","""",B1:B20000),0)")
Sheets("LookupContainer").Range("C" & res & "").Value =
UserForm3.txtSealNo.Value

what have i done wrong?
additionally, can i use range names instead of A1:A20000 & B1:B20000 and
if
so, how

Cheers
JulieD


"Tom Ogilvy" wrote in message
...
You can use an array formula:

res = Evaluate("Match(""" & containername &
""",If(A1:A20000="""","""",B1:B20000),0)")
? res
4484

You can test the results of res with

if iserror(res) then
msgbox "No unshipped container found with name " & containername
else
msgbox "found at row " & res
End if



--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
Hi Tom

thanks for your response

the situation is that i'm creating an excel "database" for a
manufacturing
company who are shipping the goods overseas in containers, and what
they
want to do is track which goods end up in each container (5 items per
container) ... initially i was told that the container number was
unique
so
therefore the match function worked fine, however, now i'm told that
the
container number isn't unique, but the container number in combination
with
a seal number is. However, the seal number is only known basically
just
before the containers are put on the ship.

the process that i'm coding at the moment checks that the items have
passed
their final inspection and are able to be packed. Then in the same
screen
i'm asking the user to nominate the proposed container for these items
to
go
into (this container might change later) ...when they enter a
container
number i want to see if the container number exists in my list where
there
is no seal number (which means that the container hasn't been shipped)
and
if it is there to continue and if it isn't to prompt the user to enter
it.

i'm looking at 15000 items so therefore 3000 containers so was hoping
for
a
'better' solution then looping through the records. It is possible to
sort
the container sheet if that would make the process quicker.

Hopefully i've explained it adequately, does this change the answer?

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
Obviously the simplest is to loop through the data and stop when the
conditions are satisfied. You can minimized the cells looked at
with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient approach
might
be
warranted. For example the first blank row marks the end of filled
cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found in
the
range.

However, i now only want to return the row number where column A
in
that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

Hi Tom

figured it out .. i was looking for the first occurance in B when A was
blank so i modified your code to
res = Evaluate("Match(""" & contname &
""",If(Unique_Container_Ref="""",Container_Number, """"),0)")

and it now works fine

Thanks
JulieD


"Tom Ogilvy" wrote in message
...
Unless you qualify the ranges with sheet name, the sheet would need to be
active - same as using the formula in a worksheet. You can use a named
range. I used the variable contnumber, but in your original code, you
used
contname. Assume the named range RNGA refers to column A in the data
sheet
and RNGB refers to column B (appropriate rows).
res = Evaluate("Match(""" & contName & _
""",If(RNGA="""","""",RNGB),0)")
if not iserror(res) then
Sheets("LookupContainer").Range("C" & res & "").Value = _
UserForm3.txtSealNo.Value
else
msgbox "Not found"
End if

Anyway, it works fine for me.
As written it supplies a string to the first argument of match. If you
are
actually looking for a number, then you need to remove the quotes and pass
in a number.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
Hi Tom

thanks for the speedy response ... i've used the formula you gave me but
i
keep getting an error 2042

Sheets("LookupContainer").Select 'only put this in to see if it
made
a difference
res = Evaluate("Match(""" & contnumber &
""",If(A1:A20000="""","""",B1:B20000),0)")
Sheets("LookupContainer").Range("C" & res & "").Value =
UserForm3.txtSealNo.Value

what have i done wrong?
additionally, can i use range names instead of A1:A20000 & B1:B20000 and
if
so, how

Cheers
JulieD


"Tom Ogilvy" wrote in message
...
You can use an array formula:

res = Evaluate("Match(""" & containername &
""",If(A1:A20000="""","""",B1:B20000),0)")
? res
4484

You can test the results of res with

if iserror(res) then
msgbox "No unshipped container found with name " & containername
else
msgbox "found at row " & res
End if



--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
Hi Tom

thanks for your response

the situation is that i'm creating an excel "database" for a
manufacturing
company who are shipping the goods overseas in containers, and what
they
want to do is track which goods end up in each container (5 items
per
container) ... initially i was told that the container number was
unique
so
therefore the match function worked fine, however, now i'm told that
the
container number isn't unique, but the container number in
combination
with
a seal number is. However, the seal number is only known basically
just
before the containers are put on the ship.

the process that i'm coding at the moment checks that the items have
passed
their final inspection and are able to be packed. Then in the same
screen
i'm asking the user to nominate the proposed container for these
items
to
go
into (this container might change later) ...when they enter a
container
number i want to see if the container number exists in my list where
there
is no seal number (which means that the container hasn't been
shipped)
and
if it is there to continue and if it isn't to prompt the user to
enter
it.

i'm looking at 15000 items so therefore 3000 containers so was
hoping
for
a
'better' solution then looping through the records. It is possible
to
sort
the container sheet if that would make the process quicker.

Hopefully i've explained it adequately, does this change the answer?

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
Obviously the simplest is to loop through the data and stop when
the
conditions are satisfied. You can minimized the cells looked at
with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient approach
might
be
warranted. For example the first blank row marks the end of
filled
cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found
in
the
range.

However, i now only want to return the row number where column A
in
that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

Your right, I coded it backwards. Sorry for the confusion.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi Tom

figured it out .. i was looking for the first occurance in B when A was
blank so i modified your code to
res = Evaluate("Match(""" & contname &
""",If(Unique_Container_Ref="""",Container_Number, """"),0)")

and it now works fine

Thanks
JulieD


"Tom Ogilvy" wrote in message
...
Unless you qualify the ranges with sheet name, the sheet would need to
be
active - same as using the formula in a worksheet. You can use a named
range. I used the variable contnumber, but in your original code, you
used
contname. Assume the named range RNGA refers to column A in the data
sheet
and RNGB refers to column B (appropriate rows).
res = Evaluate("Match(""" & contName & _
""",If(RNGA="""","""",RNGB),0)")
if not iserror(res) then
Sheets("LookupContainer").Range("C" & res & "").Value = _
UserForm3.txtSealNo.Value
else
msgbox "Not found"
End if

Anyway, it works fine for me.
As written it supplies a string to the first argument of match. If you
are
actually looking for a number, then you need to remove the quotes and
pass
in a number.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
Hi Tom

thanks for the speedy response ... i've used the formula you gave me
but
i
keep getting an error 2042

Sheets("LookupContainer").Select 'only put this in to see if it
made
a difference
res = Evaluate("Match(""" & contnumber &
""",If(A1:A20000="""","""",B1:B20000),0)")
Sheets("LookupContainer").Range("C" & res & "").Value =
UserForm3.txtSealNo.Value

what have i done wrong?
additionally, can i use range names instead of A1:A20000 & B1:B20000
and
if
so, how

Cheers
JulieD


"Tom Ogilvy" wrote in message
...
You can use an array formula:

res = Evaluate("Match(""" & containername &
""",If(A1:A20000="""","""",B1:B20000),0)")
? res
4484

You can test the results of res with

if iserror(res) then
msgbox "No unshipped container found with name " & containername
else
msgbox "found at row " & res
End if



--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
Hi Tom

thanks for your response

the situation is that i'm creating an excel "database" for a
manufacturing
company who are shipping the goods overseas in containers, and
what
they
want to do is track which goods end up in each container (5 items
per
container) ... initially i was told that the container number was
unique
so
therefore the match function worked fine, however, now i'm told
that
the
container number isn't unique, but the container number in
combination
with
a seal number is. However, the seal number is only known
basically
just
before the containers are put on the ship.

the process that i'm coding at the moment checks that the items
have
passed
their final inspection and are able to be packed. Then in the
same
screen
i'm asking the user to nominate the proposed container for these
items
to
go
into (this container might change later) ...when they enter a
container
number i want to see if the container number exists in my list
where
there
is no seal number (which means that the container hasn't been
shipped)
and
if it is there to continue and if it isn't to prompt the user to
enter
it.

i'm looking at 15000 items so therefore 3000 containers so was
hoping
for
a
'better' solution then looping through the records. It is
possible
to
sort
the container sheet if that would make the process quicker.

Hopefully i've explained it adequately, does this change the
answer?

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
Obviously the simplest is to loop through the data and stop when
the
conditions are satisfied. You can minimized the cells looked
at
with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient approach
might
be
warranted. For example the first blank row marks the end of
filled
cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is found
in
the
range.

However, i now only want to return the row number where column
A
in
that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD

Hi Tom

no problems .. just took me a minute or two to figure out why it wasn't
working.

Thanks for the assistance.

Regards
JulieD

"Tom Ogilvy" wrote in message
...
Your right, I coded it backwards. Sorry for the confusion.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi Tom

figured it out .. i was looking for the first occurance in B when A was
blank so i modified your code to
res = Evaluate("Match(""" & contname &
""",If(Unique_Container_Ref="""",Container_Number, """"),0)")

and it now works fine

Thanks
JulieD


"Tom Ogilvy" wrote in message
...
Unless you qualify the ranges with sheet name, the sheet would need to
be
active - same as using the formula in a worksheet. You can use a
named
range. I used the variable contnumber, but in your original code,
you
used
contname. Assume the named range RNGA refers to column A in the data
sheet
and RNGB refers to column B (appropriate rows).
res = Evaluate("Match(""" & contName & _
""",If(RNGA="""","""",RNGB),0)")
if not iserror(res) then
Sheets("LookupContainer").Range("C" & res & "").Value = _
UserForm3.txtSealNo.Value
else
msgbox "Not found"
End if

Anyway, it works fine for me.
As written it supplies a string to the first argument of match. If
you
are
actually looking for a number, then you need to remove the quotes and
pass
in a number.

--
Regards,
Tom Ogilvy



"JulieD" wrote in message
...
Hi Tom

thanks for the speedy response ... i've used the formula you gave me
but
i
keep getting an error 2042

Sheets("LookupContainer").Select 'only put this in to see if
it
made
a difference
res = Evaluate("Match(""" & contnumber &
""",If(A1:A20000="""","""",B1:B20000),0)")
Sheets("LookupContainer").Range("C" & res & "").Value =
UserForm3.txtSealNo.Value

what have i done wrong?
additionally, can i use range names instead of A1:A20000 & B1:B20000
and
if
so, how

Cheers
JulieD


"Tom Ogilvy" wrote in message
...
You can use an array formula:

res = Evaluate("Match(""" & containername &
""",If(A1:A20000="""","""",B1:B20000),0)")
? res
4484

You can test the results of res with

if iserror(res) then
msgbox "No unshipped container found with name " & containername
else
msgbox "found at row " & res
End if



--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
Hi Tom

thanks for your response

the situation is that i'm creating an excel "database" for a
manufacturing
company who are shipping the goods overseas in containers, and
what
they
want to do is track which goods end up in each container (5
items
per
container) ... initially i was told that the container number
was
unique
so
therefore the match function worked fine, however, now i'm told
that
the
container number isn't unique, but the container number in
combination
with
a seal number is. However, the seal number is only known
basically
just
before the containers are put on the ship.

the process that i'm coding at the moment checks that the items
have
passed
their final inspection and are able to be packed. Then in the
same
screen
i'm asking the user to nominate the proposed container for these
items
to
go
into (this container might change later) ...when they enter a
container
number i want to see if the container number exists in my list
where
there
is no seal number (which means that the container hasn't been
shipped)
and
if it is there to continue and if it isn't to prompt the user to
enter
it.

i'm looking at 15000 items so therefore 3000 containers so was
hoping
for
a
'better' solution then looping through the records. It is
possible
to
sort
the container sheet if that would make the process quicker.

Hopefully i've explained it adequately, does this change the
answer?

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
Obviously the simplest is to loop through the data and stop
when
the
conditions are satisfied. You can minimized the cells looked
at
with

Dim rng as Range
On error resume next
set rng = Columns(1).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
for each cell in rng
if Ucase(cell.offset(0,1).Value) = Ucase(contname) then
m = cell.row
exit for
end if
Next
End if

If more is known about the situation, a more efficient
approach
might
be
warranted. For example the first blank row marks the end of
filled
cells,
you could start the match process from there.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi All

i'm using the following which works well:
m = Application.WorksheetFunction.Match(UCase(contname ),
Range("Container_Number"), 0)
and returns the line number of the row where contname is
found
in
the
range.

However, i now only want to return the row number where
column
A
in
that
row
is blank e.g.

A B
1 Ref Container Number
2 AAA_001 AAA
3 AAA_002 AAA
4 AAA

so i want row 4 not row 2

what's the easiest way to do this?

Cheers
JulieD