why doesn't macro work?

[Alen32]

I got this code her:

Private Sub UserForm1_Initialize()
Dim rFound As Range
Dim sFirstAdd As String
Dim rLook As Range
Dim rValue As Range

Set rValue = Ark1.Range("A5")
Set rLook = Ark11.Range("A10:A250")
Me.ListBox1.ColumnCount = 4

Set rFound = rLook.Find(rValue.Value, , , xlWhole)

If Not rFound Is Nothing Then
sFirstAdd = rFound.Address

Do
With Me.ListBox1
.AddItem rFound.Row
.List(.ListCount - 1, 1) = rFound.Value
.List(.ListCount - 1, 2) = rFound.Offset(0, 2).Value
.List(.ListCount - 1, 3) = rFound.Offset(0, 5).Value
End With

Set rFound = rLook.FindNext(rFound)
Loop Until rFound.Address = sFirstAdd
End If

End Sub
I have made command button on sheet1 which display userform with listbox,
but listbox is empty.

 

[Bob Phillips]

Change

Private Sub UserForm1_Initialize()

to

Private Sub UserForm_Initialize()


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

When you step through the code, are you sure that the rValue.value was found?

Excel will remember the last parameters that you specified in the Find
dialog--either manually or via code. I've always found it better to specify
everything and take no chances. (Maybe matchcase:=true and that's causing a
problem???)

 

[Dave Peterson]

Oops. I missed what Bob found!

When you step through the code, are you sure that the rValue.value was found?

Excel will remember the last parameters that you specified in the Find
dialog--either manually or via code. I've always found it better to specify
everything and take no chances. (Maybe matchcase:=true and that's causing a
problem???)

 

[Alen32]

I did change as bob siad but I get error message
"run time error 424"

 

[Bob Phillips]

Do you have that code in the form code module?

Which line do you get the error at?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

In the vbe, when you look at your project in the project explorer, are your
sheets listed as

ark1(ark1)

or as

sheet1(ark1)

if the latter,
then

Private Sub UserForm1_Initialize()
Dim rFound As Range
Dim sFirstAdd As String
Dim rLook As Range
Dim rValue As Range

Set rValue = Sheet1.Range("A5")
Set rLook = Sheet11.Range("A10:A250")
Me.ListBox1.ColumnCount = 4

Set rFound = rLook.Find(rValue.Value, , , xlWhole)

If Not rFound Is Nothing Then
sFirstAdd = rFound.Address

Do
With Me.ListBox1
.AddItem rFound.Row
.List(.ListCount - 1, 1) = rFound.Value
.List(.ListCount - 1, 2) = rFound.Offset(0, 2).Value
.List(.ListCount - 1, 3) = rFound.Offset(0, 5).Value
End With

Set rFound = rLook.FindNext(rFound)
Loop Until rFound.Address = sFirstAdd
End If

End Sub

 

[Alen32]

I clicked double on userform and then I inserted code.
Private Sub UserForm_Initialize()
Dim rFound As Range
Dim sFirstAdd As String
Dim rLook As Range
Dim rValue As Range

Set rValue = Ark1.Range("A5")
Set rLook = Ark11.Range("A10:A250")
Me.ListBox1.ColumnCount = 4

Set rFound = rLook.Find(rValue.Value, , , xlWhole)

If Not rFound Is Nothing Then
sFirstAdd = rFound.Address

Do
With Me.ListBox1
.AddItem rFound.Row
.List(.ListCount - 1, 1) = rFound.Value
.List(.ListCount - 1, 2) = rFound.Offset(0, 2).Value
.List(.ListCount - 1, 3) = rFound.Offset(0, 5).Value
End With

Set rFound = rLook.FindNext(rFound)
Loop Until rFound.Address = sFirstAdd
End If


End Sub

 

[Bob Phillips]

Have you seen Tom's response?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

I neglected to incorporate the previous correction of changing Userform1 to
Userform - don't view it that I am suggesting it should be Userform1.
Userform alone is correct.