advanced filter, goto next line(record)

[Pierre]

Hi experts,

I have a databasesheet called "datadga"

I have a userform with a combobox with unique values from column "W" in the
"datadga" sheet
When a user selects a value from the combobox and klick on a button called
"filter" the "datadga" sheet is filtered on the value in the combobox.

Next, I want all textboxes filled with items from the first row in the
filtered list.
Problem 1:
I can't manage to do so because i keep getting the first row of the entire
database and not the filtered one !
Any suggestions ont this problem would be most welcom !

Problem 2:
I have two extra buttons called "next" and "previous"
clicking on these buttons should result in going to the next row in the
filtered database and displaying all data in the textboxes on the userform.
Here i have the same problem, i can't manage to do so because i keep getting
the first row of the entire database and not the filtered one !

Any help would be greatly appreciated !
Pierre

 

[Incidental]

Hi Pierre

the code below should do what you want, i set up a userform with 3
textboxes a combobox and three buttons (1 to filter and a Next and
Previous button) and used this code to filter data held in the range
A1:C20 and populate the textboxes.

Option Explicit
Dim MyRng As Range

Private Sub CmdFilter()

With Sheets("DataDGA")

Range("A1:C20").Select

Selection.AutoFilter Field:=1, Criteria1:=ComboBox1.Value

Set MyRng = Cells.SpecialCells(xlCellTypeVisible).Cells(1)

Do

Set MyRng = MyRng.Offset(1, 0)

Loop While MyRng.EntireRow.Hidden = True

Call CellsToTextBoxes

End With

End Sub

Private Sub CmdPrev_Click()

Do

Set MyRng = MyRng.Offset(-1, 0)

Loop While MyRng.EntireRow.Hidden = True

Call CellsToTextBoxes

End Sub

Private Sub CmdNext_Click()

Do

Set MyRng = MyRng.Offset(1, 0)

Loop While MyRng.EntireRow.Hidden = True

Call CellsToTextBoxes

End Sub

Sub CellsToTextBoxes()

TextBox1.Value = MyRng.Value

TextBox2.Value = MyRng.Offset(0, 1).Value

TextBox3.Value = MyRng.Offset(0, 1).Value

End Sub

Hope this helps you out

Steven

 

[Pierre]

Hi Steven,

Thanks for your help.
Unfortunately, i do not understand it completely.

Can you explain what i should do at the statement;

Call CellsToTextBoxes

How should i replace that with a code to fill the textboxes ?

I now have the following code to filter and then it counts the number of
lines filtered

Private Sub but_filter_Click()
'FILTER ON
Dim row_count As Double
Dim matched_criteria As Double
Dim check_row As Double
Dim datateller As Double
Columns("w:w").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=zoek_acc.Value
Set filterrange = Cells.SpecialCells(xlCellTypeVisible).Cells(1) 'your
suggestion !
Range("a1").Select
Selection.CurrentRegion.Select
row_count = Selection.Rows.Count - 1 ' Count the rows and subtract
the header.
matched_criteria = 0 ' Set variable to
zero
check_row = 0 ' Set variable to zero.
While Not IsEmpty(ActiveCell) ' Check to see if row height is
zero.
ActiveCell.Offset(1, 0).Select
If ActiveCell.RowHeight = 0 Then
check_row = check_row + 1
Else
matched_criteria = matched_criteria + 1
End If
Wend

Next i have the following code for the button "previous" (which does not
work)

Private Sub but_vorige_Click()
Do
Set filterrange = filterrange.Offset(-1, 0)
Loop While filterrange.EntireRow.Hidden = True
Call CellsToTextBoxes
End Sub

This gives an 1004 errormessage on: Loop While filterrange.EntireRow.Hidden
= True

Any suggestions on this?
Thanks,
Pierre

 

[Pierre]

Hi Steve,

This helps a lot.
However, there is one last problems;

1.
If i filter the data ican hit the button "next" or "previous". So far so
good.
However, i can keep on hitting these buttons and that should not be
possible.
Hitting the button "previous" should stop when the first row of the
selection is reached.
Hitting the button "next" should stop when the last row of the selection is
reached.

2. I would like to know the number of rows it finds when a filter is set.

I would greatly appreciate your input.
Thanks,
Pierre