Help with Next button on userform

[redheadincognito]

Depsite an extensive amount of time searching through the forums, I find myself still having an issue getting the "next" button on my userform to actually get the next item in the database. I wish it to stop once a match is found, fill out the appropriate textboxes on the userform. Then once the button is pressed again, it should go on to the next. but it goes straight to the msgbox stating there are no further entries.
Now, at this pont, the userform will allready hold the information from row 1 (A3 in this code.) and have autofiltered so that the value of combobox1 are the only visible rows. So could that be my problem? I thought I had it covered in here, but something is not right.
I am still a rookie, and this is my first real attempt at anything worthwhile. (no pro though, I am not making money for my efforts, all for learning and efficency.) I have tried and tried with this should be easy to most code. Any help would be GREATLY appreciated. Thank you in advance.

Private Sub CommandButton7_Click()
Sheet65.Select
Dim rng As Range, cell As Range
iName = UserForm23.ComboBox1.Value
Set rng = ActiveSheet.Range("A3:A500")
For Each cell In rng
iBegin:
' check if row is visible - if so, get it
If cell.EntireRow.Hidden = False Then
' process this row
If cell.Offset(1, 0).Value = iName Then
cell.Offset(1, 0).Select
UserForm23.TextBox2.Value = cell.Offset(0, 1).Value
UserForm23.TextBox4.Value = cell.Offset(0, 5).Value
UserForm23.ComboBox2.Value = cell.Offset(0, 2).Value
UserForm23.ComboBox3.Value = cell.Offset(0, 3).Value
UserForm23.ComboBox4.Value = cell.Offset(0, 3).Value
GoTo iEnd
Else
' check if row is hidden - if so, skip it and return to loop
If cell.EntireRow.Hidden = True Then
cell.Offset(1, 0).Select
Exit For
GoTo iBegin
Else
' Determine end of matches
If cell.Offset(1, 0).Value = "" Then
MsgBox "No further entries found.", vbInformation + vbOKOnly, "Error"
iEnd:
Exit For
End If
End If
End If
End If
Next
End Sub
It may not even be correct, or there may be major errors, but I am learning, and it seems to make logical sense in my head.
Thank you again, sorry for the length.
Dave

 

[Diablo II]

have you tryed to step thro your code ?

 

[Diablo II]

ooo the first thing i see is the Sheet65.Select this should be sheets("sheet65").select or sheets("sheet65").activate

 

[redheadincognito]

Thank you, I have stepped through it, I still haven't gotten it going, but I did find the If statement to find hidden cells redundant i think. also thank you for the sheet selecting tip. like I said, I'm brand new, and I appreciate your feedback.

 

[redheadincognito]

OK, So I now have 2 issues that I need help with, one is this code will go to the next item as long there are not more than 2 rows sperating them, otherwise it gives me the no further entries end script. And Two, It will not recognize the end of the filtered names and run the no further entries when i want it to. It simply skips down and fills the form in as blank entries. I would appreciate a little bit of explanation as to my issues, so that I can learn from this. Thank you all in advance once again. The updated code is as follows....

Private Sub CommandButton7_Click()
Sheets("Segmentor").Select
Dim rng As Range, cell As Range
iName = UserForm23.ComboBox1.Value
Set rng = ActiveCell
For Each cell In rng
iBegin:
' check if row is visible - if so, get it
If ActiveCell.Offset(1, 0).EntireRow.Hidden = True Then
ActiveCell.Offset(1, 0).Select
GoTo iBegin:
Else
' process this row
If cell.Value = iName Then
ActiveCell.Offset(1, 0).Select
UserForm23.TextBox2.Value = ActiveCell.Offset(0, 1).Value
UserForm23.TextBox4.Value = ActiveCell.Offset(0, 5).Value
UserForm23.ComboBox2.Value = ActiveCell.Offset(0, 2).Value
UserForm23.ComboBox3.Value = ActiveCell.Offset(0, 3).Value
UserForm23.ComboBox4.Value = ActiveCell.Offset(0, 3).Value
Else
If Not cell.Value = iName Then
GoTo iEnd
Exit For
End If
End If
End If
Next
Exit Sub
iEnd:
MsgBox "No further entries found.", vbInformation + vbOKOnly, "Error"
End Sub

 

[redheadincognito]

Alright, so after much more trying to learn here, i have been advised on other forums that the go to's are "totally, totally forbidden." but with no further to go on, can anyone take a minute to help point a newbie in the right direction with more than a one liner. unless that one leads me to more answers that is. It is becoming obvious that I was nowhere in the right direction, so anyone that can help, I will be forever grateful.

 

[Tom Urtis]

I'm not following everything you are doing, especially the matching part, but generally, GoTo statements are not the AntiChrist, neither are the SendKeys or Selection methods, but I digress.

However, how and why you use what methods you use, and especially how you (generic "you" as the workbook designer) arrive at the reasons for needing GoTo statements can give rise to problems in the code if you could have used a more intuitive approach that does not lead to spaghetti code.

Since you are filtering, you can use the specialcells property for visible cells only to do something with visible cells in the filtered range.

Example, if your header row is row 1, and you are examining values in column A that passed throgh the filter and are hence visible, you can do this:

Dim FilterRange as Range, cell as range
Set FilterRange = Range("A1").CurrentRegion
 
'the filter part comes here, only you know that but we don't because you didn't post it).
 
'then...
 
With FilterRange
for each cell in .Offset(1).resize(.rows.count - 1, 1).specialcells(xlCellTypeVisible)
 
'populate your textboxes here or whatever you are doing
 
Next cell
 
End With

You asked for more than a one-liner and there it is, but you also can explain a little better what you are doing, such as with a screen shot of your worksheet layout.