Solved: how can i make this arry work

[rrosa1]

hi
i have some code in my old work book & i 'm trying to change a little but it's give me error run time error: 1004 " Unable to get the Find property of the worksheet function class"
can any one help me pl
hear is the code i 'm attaching also sample work book.
my goal is to populate "ComboBox1" by fixed value from arry but minus the value which is already in the sheet "Today"
any help is appreciated.

Private Sub UserForm_Initialize()
Dim aryMonths As Variant
Dim ii As Long
Dim ws As Worksheet
Dim iRow As Long
Set ws = ThisWorkbook.Worksheets("today")

'find first empty row in today sh
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
aryMonths = Array("101", "201", "202", "301", "302")

For ii = LBound(aryMonths) To UBound(aryMonths)
If Not WorksheetFunction.Find(ws.Range("A8", ws.Cells(iRow, 1)), aryMonths(ii)) Then
Me.ComboBox1.AddItem aryMonths(ii)
End If

Next
End Sub

[Trebor76]

Hi rrosa1,

Try this:


Private Sub UserForm_Initialize()

Dim aryMonths As Variant
Dim ii As Long
Dim ws As Worksheet
Dim iRow As Long
Dim rngFoundCell As Range

Set ws = ThisWorkbook.Worksheets("today")

'find first empty row in today sh
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
aryMonths = Array("101", "201", "202", "301", "302")

For ii = LBound(aryMonths) To UBound(aryMonths)
With ws.Range("A8", ws.Cells(iRow, 1))
Set rngFoundCell = .Find(aryMonths(ii), LookIn:=xlValues)
End With
If rngFoundCell Is Nothing Then
Me.ComboBox1.AddItem aryMonths(ii)
End If
Next ii

End Sub

Regards,

Robert

[rrosa1]

Ty Trebor76
for quick response but the find func. only look in 1 st line of sh and add the item in combo box, but i need to compare the array with all value in sh and if it find value in sh then do not add that value but only add array value which is not in the sh like following ex
value in sh
A1

201
302
301

then only show value of array like below
101
202
it's basically diminishing list value if value exist then do not add in combo box
i hope i explain my self clearly
but thank you for yr help

[Teeroy]

Hi rrosa1,

Try this:

Private Sub UserForm_Initialize()
Dim aryMonths As Variant, aryShtMonths
Dim ii As Long
Dim ws As Worksheet
Dim iRow As Long
Set ws = ThisWorkbook.Worksheets("today")
'find first empty row in today sh
iRow = ws.Cells(ws.Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
aryMonths = Array("101", "201", "202", "301", "302")
aryShtMonths = Application.Transpose(ws.Range("A8:A" & iRow))
For ii = LBound(aryMonths) To UBound(aryMonths)
If IsError(Application.Match(Val(aryMonths(ii)), aryShtMonths, 0)) Then
Me.ComboBox1.AddItem aryMonths(ii)
End If
Next
End Sub
I think part of the problem is that you are trying to compare numerals as text strings against integers.

[rrosa1]

Hi Teeroy
thank you it's work great .!!!
one more Q is there any way i can make all integers or all strings or keep them numerals.so it don't act on in future .
thanks for yr help

[snb]

I'd use this approach

Private Sub UserForm_Initialize()
ComboBox1.list=Array(101, 201, 202, 301, 302)
For j=0 to combobox1.listcount-1
If not iserror(application.match(combobox1.list(j),ThisWorkbook.Worksheets("today").columns(1),0)) then combobox1.removeitem j
Next
End Sub

[rrosa1]

ty snb
it's short & sweet but it's add the one nos (201) item which already in the list so can u pl look that one
thanks snb
by the way i change yr code little bit since it was give me error so,i'm sure it might be typo on yr part, i'm not correcting u but i just check and it give me error so i try my best,i'm just learning vba.


Private Sub UserForm_Initialize()
Dim j
ComboBox1.List = Array(101, 201, 202, 301, 302)
For j = 0 To ComboBox1.ListCount - 1
If Not IsError(Application.Match(ThisWorkbook.Worksheets("today").Columns(1), 0)) Then ComboBox1.RemoveItem j
Next
End Sub

i just remove "it" before ( Thisworkbook ,is it some thing m'i doing wrong or just let me know
thanks

[snb]

I amended my previous suggestion

[rrosa1]

hi snb
i checked yr new code but
it give me run time '381'
"could not get the list property,invalid property array index"
can u pl help.

[Teeroy]

There is a small problem with SNB's code; as you remove items your listcount decreases so you eventually call a list item which doesn't exist. A minor amendment is need to replace the FOR-NEXT loop with a DO-WHILE loop so that the listcount is constantly recaluated. Try:

Private Sub UserForm_Initialize()
Dim j As Integer
ComboBox1.List = Array(101, 201, 202, 301, 302)
j = 0
Do
If Not IsError(Application.Match(ComboBox1.List(j), ThisWorkbook.Worksheets("today").Columns(1), 0)) Then
ComboBox1.RemoveItem j
Else
j = j + 1
End If
Loop While j <= ComboBox1.ListCount - 1
End Sub

[rrosa1]

thank you Teeroy
it work great
thank you all for yr help