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