conditional match function

[JulieD]

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

 

[Tom Ogilvy]

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.

 

[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]

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

 

[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]

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.

 

[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]

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

 

[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