PDA

View Full Version : Solved: function match : error in vba



choubix
07-10-2008, 02:33 AM
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

workbooks("myworkbook.xls").worksheets("Securities")



For i = LBound(aSecurities, 1) To UBound(aSecurities, 1)
Control = aSecurities(i, 3)
Duplicates = Application.WorksheetFunction.Match(Control, wsSecurities.Columns("D: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


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
07-10-2008, 03:25 AM
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
07-10-2008, 03:38 AM
The Application.WorksheetFunction.Match function throws a vba error if a match is not found. You should try


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

choubix
07-10-2008, 04:11 AM
hi Bg52 thnaks for your reply

i've tried this:


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:D"), 0)
If Err <> 0 Then Duplicates = 0
On Error GoTo 0
If Duplicates > 0 Then
MsgBox "test"
End If


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
07-14-2008, 07:12 AM
completely forgot to say thanks!
after a bit of tweaking it worked just fine ... :)