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.

why doesn't macro work?
 

Change

Private Sub UserForm1_Initialize()

to

Private Sub UserForm_Initialize()


--

HTH

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


"Alen32" wrote in message
lkaboutsoftware.com...
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.

why doesn't macro work?
 

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

why doesn't macro work?
 

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

why doesn't macro work?
 

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

why doesn't macro work?
 

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" wrote in message
lkaboutsoftware.com...
I did change as bob siad but I get error message
"run time error 424"

why doesn't macro work?
 

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" wrote in message
lkaboutsoftware.com...
I did change as bob siad but I get error message
"run time error 424"

why doesn't macro work?
 

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

why doesn't macro work?
 

Have you seen Tom's response?

--

HTH

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


"Alen32" wrote in message
lkaboutsoftware.com...
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

why doesn't macro work?
 

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" wrote in message
...
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" wrote in message
lkaboutsoftware.com...
I did change as bob siad but I get error message
"run time error 424"