Using Find & FindNext in a form

[BernzG]

Hi,

Can someone please help me here - trying to get Find and FindNext to work within to populate data within a form to enable it to be edited.

I have many records with the same number in Col A in the database and this seems to work in finding the first record and then the next but not after that.



Private Sub CommandButton1_Click()

Dim Loc As String

Loc = Format(TextBox1.Value, "####")

Sheets("Sheet1").Select
Cells.Find(What:=Loc, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate


With Worksheets("Sheet1")
Set C = .Range("A:A").Find(Loc, LookIn:=xlValues)

If Not C Is Nothing And .Cells(C.Row, 1) <> Loc Then firstAddress = C.Address
Do
Set C = .Range("A:A").FindNext(C)
Loop While Not C Is Nothing And .Cells(C.Row, 1) <> Loc And C.Address <> firstAddress

TextBox2 = .Cells(C.Row, 2)
TextBox3 = .Cells(C.Row, 3)
End With

End Sub

Cheers,
Bernz

[Patrick Molloy]

Add a listbox (defaults to listbox1) and add the code below to the userform
code, replacing your code.

I can see that you have been reading help, but had a few issues.
First, you loop through the cells using find, but you try to populate text
boxes at the end, after the loop. The code I wrote populates a list box
during the loop.
What you need to add is a listbox click event that will populate the two
text boxes

Option Explicit
Private Sub CommandButton1_Click()

Dim Loc As String
Dim found As Range

Loc = Format(TextBox1.Value, "####")
ListBox1.Clear

With Worksheets("Sheet1")

Set found = .Cells.Find(What:=Loc)

If Not found Is Nothing Then
Loc = found.Address
Do
ListBox1.AddItem .Cells(found.Row, 2)
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(found.Row,
3)

Set found = .Cells.FindNext(found)

Loop While found.Address <> Loc
End If
End With

End Sub


Private Sub ListBox1_Click()
With ListBox1
TextBox2 = .List(.ListIndex, 0)
TextBox3 = .List(.ListIndex, 1)
End With
End Sub

Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 2
End Sub




"BernzG" wrote:

>
> Hi,
>
> Can someone please help me here - trying to get Find and FindNext to
> work within to populate data within a form to enable it to be edited.
>
>
> Private Sub CommandButton1_Click()
>
> Dim Loc As String
>
> Loc = Format(TextBox1.Value, "####")
>
> Sheets("Sheet1").Select
> Cells.Find(What:=Loc, After:=ActiveCell, LookIn:=xlValues, LookAt:=
> _
> xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
>
>
> With Worksheets("Sheet1")
> Set C = .Range("A:A").Find(Loc, LookIn:=xlValues)
>
> If Not C Is Nothing And .Cells(C.Row, 1) <> Loc Then firstAddress =
> C.Address
> Do
> Set C = .Range("A:A").FindNext(C)
> Loop While Not C Is Nothing And .Cells(C.Row, 1) <> Loc And
> C.Address <> firstAddress
>
> TextBox2 = .Cells(C.Row, 2)
> TextBox3 = .Cells(C.Row, 3)
> End With
>
> End Sub
>
> Cheers,
> Bernz
>
>
> --
> BernzG
> ------------------------------------------------------------------------
> BernzG's Profile: http://www.excelforum.com/member.php...o&userid=22949
> View this thread: http://www.excelforum.com/showthread...hreadid=395711
>
>

[BernzG]

Hi Patrick,

Thanks for this tried it and it works okay.

Have now modified it slightly to show the list box results after textbox1 has been updated. Once data has been entered into textbox1, textbox1 is hidden and the listbox now appears with all the records with the same ID as in textbox1. You can select an entry in the listbox and the data in TextBox2 & 3 are updated.

Cheers,
Bernz

Private Sub TextBox1_AfterUpdate()

Dim Loc As String

Loc = Format(TextBox1.Value, "####")

Sheets("Sheet1").Select
Cells.Find(What:=Loc, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

With Worksheets("Sheet1")
Set c = .Range("B:B").Find(Loc, LookIn:=xlValues)

TextBox2 = .Cells(c.Row, 3)
TextBox3 = .Cells(c.Row, 4)

End With

ListBox1.Visible = True
TextBox1.Visible = False

ListBox1.Clear

With Worksheets("Sheet1")

Set found = .Cells.Find(What:=Loc)

If Not found Is Nothing Then
Loc = found.Address
Do
ListBox1.AddItem .Cells(found.Row, 3)
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(found.Row, 4)

Set found = .Cells.FindNext(found)

Loop While found.Address <> Loc
End If
End With

End Sub


Private Sub ListBox1_Click()

With ListBox1
TextBox2 = .List(.ListIndex, 0)
TextBox3 = .List(.ListIndex, 1)

End With

End Sub

Private Sub UserForm1_Initialize()

ListBox1.ColumnCount = 2

End Sub

[BernzG]

Hi Patrick.

Well have come across another problem when using the listbox.

Have extended the size of my database and now I get the following error message

“ Runtime Error 380 – Could not set the list property. Invalid property value.”



Private Sub UserForm1_Initialize()

ListBox1.ColumnCount = 18

End Sub





EXTRACT from macro in Userform

Loc = TextBox1.Value
ListBox1.Clear

With Worksheets("Dbase")

Set found = .Cells.Find(What:=Loc)

If Not found Is Nothing Then
Loc = found.Address
Do
ListBox1.AddItem .Cells(found.Row, 4)
ListBox1.List(ListBox1.ListCount - 1, 1) = .Cells(found.Row, 2)
ListBox1.List(ListBox1.ListCount - 1, 2) = .Cells(found.Row, 3)
ListBox1.List(ListBox1.ListCount - 1, 3) = .Cells(found.Row, 5)
ListBox1.List(ListBox1.ListCount - 1, 4) = .Cells(found.Row, 6)
ListBox1.List(ListBox1.ListCount - 1, 5) = .Cells(found.Row, 7)
ListBox1.List(ListBox1.ListCount - 1, 6) = .Cells(found.Row, 8)
ListBox1.List(ListBox1.ListCount - 1, 7) = .Cells(found.Row, 9)
ListBox1.List(ListBox1.ListCount - 1, 8) = .Cells(found.Row, 10)
ListBox1.List(ListBox1.ListCount - 1, 9) = .Cells(found.Row, 11)
ListBox1.List(ListBox1.ListCount - 1, 10) = .Cells(found.Row, 12) “Debug error message here”
ListBox1.List(ListBox1.ListCount - 1, 11) = .Cells(found.Row, 13)
ListBox1.List(ListBox1.ListCount - 1, 12) = .Cells(found.Row, 14)
ListBox1.List(ListBox1.ListCount - 1, 13) = .Cells(found.Row, 15)
ListBox1.List(ListBox1.ListCount - 1, 14) = .Cells(found.Row, 16)
ListBox1.List(ListBox1.ListCount - 1, 15) = .Cells(found.Row, 17)

Set found = .Cells.FindNext(found)

Loop While found.Address <> Loc
End If
End With

Can't understand. look forward to hearing from you.

Cheers,
Bernz