why doesn't macro work?

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.

Change

Private Sub UserForm1_Initialize()

to

Private Sub UserForm_Initialize()


--

HTH

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


"Alen32" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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 wrote:
>
> 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.

--

Dave Peterson

Oops. I missed what Bob found!

Dave Peterson wrote:
>
> 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 wrote:
> >
> > 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.
>
> --
>
> Dave Peterson

--

Dave Peterson

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

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)


"Alen32" <[email protected]> wrote in message
news:[email protected]...
> I did change as bob siad but I get error message
> "run time error 424"
>

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

--
Regards,
Tom Ogilvy


"Alen32" <[email protected]> wrote in message
news:[email protected]...
> I did change as bob siad but I get error message
> "run time error 424"
>

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

Have you seen Tom's response?

--

HTH

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


"Alen32" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:uor1Bd%[email protected]...
> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Alen32" <[email protected]> wrote in message
> news:[email protected]...
> > I did change as bob siad but I get error message
> > "run time error 424"
> >
>
>