why doesn't macro work?

A

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.
 
B

Bob Phillips

Change

Private Sub UserForm1_Initialize()

to

Private Sub UserForm_Initialize()


--

HTH

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

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???)
 
D

Dave Peterson

Oops. I missed what Bob found!

Dave said:
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???)
 
B

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)
 
T

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
 
A

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
 
B

Bob Phillips

Have you seen Tom's response?

--

HTH

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top