Solved: function match : error in vba

[choubix]

hello, in the following code the line with the Match function reutrns an error (object is not set)
I suspect the wsSecurities to be "guilty" for this...

wsSecurities est Set dans ThisWorkbook / Open being

[vba]workbooks("myworkbook.xls").worksheets("Securities")[/vba]


[vba]
For i = LBound(aSecurities, 1) To UBound(aSecurities, 1)
Control = aSecurities(i, 3)
Duplicates = Application.WorksheetFunction.Match(Control, wsSecurities.Columns("D"), 0)
If Duplicates > 0 Then
MsgBox ok! 'will copy the data stored in the array in the database
On Error Resume Next 'go to next iteration
End If
Next i
[/vba]

what i am trying to achieve here: if Duplicates returns a value then I won't insert the data from this array into the database and proceed to the next one.

any idea ?

thx!

[choubix]

seems that I have 2 problems here:

if I hard code "control" for something that is in the database AND replace "wsSecurities" for Worksheets("Securities"). ... it opens the msgbox...

[bg52]

The Application.WorksheetFunction.Match function throws a vba error if a match is not found. You should try

[VBA]
On Error Resume Next
Duplicates = Application.WorksheetFunction.Match(Control, wsSecurities.Columns("D"), 0)
If Err <> 0 then Duplicates = 0
On Error Goto 0
[/VBA]

[choubix]

hi Bg52 thnaks for your reply

i've tried this:
[vba]

For i = LBound(aSecurities, 1) To UBound(aSecurities, 1)
On Error Resume Next
Control = aSecurities(i, 3)
Duplicates = Application.WorksheetFunction.Match(Control, Worksheets("Securities").Columns("D"), 0)
If Err <> 0 Then Duplicates = 0
On Error GoTo 0
If Duplicates > 0 Then
MsgBox "test"
End If
[/vba]

but it appears that I am having 2 problems here:
"Control" is loaded with the correct value from the combobox BUT doesnt seem to be interpreted correctly by Application.WorksheetFunction.Match (Duplicates is Empty)
if I hardcode the value instead of using Control it work...

moreover:
i'm now using Worksheets("Securities"). instead of wsSecurities. ... (seems the varialbe is not set.

I set the variable wsSecurities in thisworkbook / open AND I make it publix (in Thisworkbook too, before "open")

dont get why both Control and wsSecurities don't work now...

[choubix]

completely forgot to say thanks!
after a bit of tweaking it worked just fine ...