Solved: Match function in Vba

[choubix]

hello,

I still have a problem with my Match function in the following code:


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


Control is a Variant

The variable "Control" contains a value from a Combobox apssed on to an array BUT Duplicates always returns 0.

if I hard code the value of "Control" (replace it for either an existing or a non existing value in the database) it works correctly (Duplicates is either 0 or >0)

any idea of why this happens please?

thx :)

[Bob Phillips]

We would have to see what the typing of Control was, and what was in the aSecurities array before we could make any suggestions.

[choubix]

hi Xld,

the code in full looks like this:


Dim i As Integer
Dim Control As Variant
Dim Duplicates As Variant
Dim aSecurities()
Call Constants
'we are using a multidim arrayto store the valuesof the comboboxes
'we exit the loopwhen no security type has been entered in the form
ReDim aSecurities(1 To 6, 4)
For i = 1 To 6
Control = Controls("Valoren" & i).Value
If Control = "" Then Exit For
aSecurities(i, 1) = Controls("Sectype" & i).Value
aSecurities(i, 2) = Controls("Zone" & i).Value
aSecurities(i, 3) = Controls("Valoren" & i).Value
aSecurities(i, 4) = Controls("Ticker" & i).Value
Next i

For i = LBound(aSecurities, 1) To UBound(aSecurities, 1)
On Error Resume Next
Control = aSecurities(i, 3)
Duplicates = Application.WorksheetFunction.Match(Control, wsSecurities.Columns("D:D"), 0)
If Err <> 0 Then Duplicates = 0
On Error Goto 0
If Duplicates > 0 Then
MsgBox "test"
End If


btw: is possible in a combobox to show a list but when a value is chosen to return a numric value instead? eg: list is : ice cream, cake, coca. the user chooses ice cream, the value returned will be 1, cake would be 2, coca would be 3 ...)

thx!

[Bob Phillips]

We stilkl can't see this



Control = Controls("Valoren" & i).Value


why not post a workbook?

[choubix]

because i work with clients' data each time so i cant post a workbook easily...

in this control I pass the Valoren value (it's a unique id of 11 characters ) that the user inputs in a combobox to the variable Control.

I use it to check whether the user is going to duplicate data

[Bob Phillips]

I think it is impossible to help then. Match works, so there is something in your data, setup that stops it,a nd if we cannot see it, we cannot solve it.

If you can't create a workbook that shows this behaviour, we are at an impasse.

[choubix]

think I made enough changes for it to be posted on the board ;)

really appreciate your help XLD.
btw: when the navigation form is fired up you can click on the top left button. otherwise: the code is in New_Sec


quick question: is it possible, when the user selects a value in the combobox , to return A, B, S or E instead of "alt invt, Bonds, Str pdt and Equity" ??

ps: have to change the name of the file to myport.xls instead of myport2.xls

thx

[Bob Phillips]

Try this



Duplicates = Application.Match(Val(Control), wsSecurities.Columns("D:D"), 0)

[choubix]

hi xls, thanks!
jsut a quick question: the valoren id is composed of both numeric and non numeric characters.
I read while google for val() that it would stop when it encounters the first non numeric characters.

that means that I may have duplicates in the end
would you know another function of the same kind as val() that would make my Match function work with the valoren id please?
thanks

[Bob Phillips]

If TypeName(Control) = "String" Then

Duplicates = Application.Match(Control, wsSecurities.Columns("D:D"), 0)
Else

Duplicates = Application.Match(Val(Control), wsSecurities.Columns("D:D"), 0)
End If

[choubix]

thanks Xld! it works :)
you're a 5 star programmer... thanks for taking of your time to help others (and me especially! ;) )