why doesn't macro work?

  • Thread starter Thread starter Alen32
  • Start date Start date
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.
 
Change

Private Sub UserForm1_Initialize()

to

Private Sub UserForm_Initialize()


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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???)
 
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???)
Click to expand...
 
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)
 
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
 
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)
 
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.
 
Back
Top