Hi,
Eralier I post a question, but couldn`t get the answer but I think I fixed
my problem. But still I think in a weird wayI have to array one is e6:e20
second is pmlist. i have a code which randomly assign values from the array
pmlist.In the end I want to see the values from "pmlist" which is not
assigned to e6:e20 array.I couldn`t make it but instead I wrote a formula in
that "pmlist" array in the excel sheet which can tell me those values
assigned or not then wrote a following code to show those not assigned in the
list box.:
Option Explicit
Private pmlist As Range

Private Sub UserForm_Initialize()
Set pmlist = ThisWorkbook.Names("pmlist").RefersToRange
Dim Cell As Range
For Each Cell In pmlist.Columns(1).Cells
If Cell.Value = "not rostered" Then
ListBox2.AddItem Cell.Offset(0, 3).Value
ListBox2.List(ListBox2.ListCount - 1, 1) = Cell.Offset(0, 4)
ListBox2.List(ListBox2.ListCount - 1, 2) = UCase(Cell.Offset(0, 4)) '
ListBox2.TextColumn = 3
End If
Next
End Sub
Private Sub UserForm_Terminate()
Set pmlist = Nothing
End Sub
But this look very long way to do it,anybody help me to make a short cut

--
Regards,
Baha