Userfor VBA code update

[Damian37]

Hello all,
I just discovered something while testing a userform within an Excel spreadsheet I'm working on that Stnkynts was nice enough to help me with. The userform searches through my data tab for specific records in order to edit them. While testing, I created multiple records using the same Unique ID. Now when I enter the ID in order to pull back the records, I am only retrieving the first record with that ID, the other records below that first one are not being retrieved using the userform. I know I have to enter a loop within my script so the search command button will go to the next record. When I attempted to enter a loop line into the script I can see the code is looping, but a new record isn't coming back into the userform, and my cursor appears to just cycle endlessly. What am I missing that will allow the userform to bring back the next record with the same record ID without cycling endlessly yet bringing nothing back? I've pasted the code I have, and I've also attached an example of the userform and the records I am attempting to retrieve. Thank you for any assistance.

Damian37

Please Login or Register  to view this content.

[Richard Buttrey]

Hi

I question whether you should be looping through your records merely to edit them. Looping should be the last resort since it's inherently slow and inefficient. Filtering data is the fastest way I know of doing this sort of stuff.

So instead use a simple Advanced Data Filter to filter your data 'InPlace' so that only your required records are visible. Then use the .SpecialCells(xlCellTypeVisible) to identify these filtered cells and edit the cells as appropriate.

[Damian37]

Hi

I question whether you should be looping through your records merely to edit them. Looping should be the last resort since it's inherently slow and inefficient. Filtering data is the fastest way I know of doing this sort of stuff.

So instead use a simple Advanced Data Filter to filter your data 'InPlace' so that only your required records are visible. Then use the .SpecialCells(xlCellTypeVisible) to identify these filtered cells and edit the cells as appropriate.

This can be done within a userform? Currently I input a record number into my userform, and the form returns all fields associated with that record. If I use advanced data filter within my code, will it allow me to click on the search button until my desired record appears?

[Richard Buttrey]

I was merely suggesting that rather than loop through individual records one at a time to incorporate them in a user form that you first grab all the data and if necessary load the lot in one go.

I would however question whether it's more efficient to use a user form or just control the way the user edits the relevant rows on the data sheet itself.
I tend to prefer using standard Excel functionality in the first instance if it's reasonable and acceptable to do so rather than starting with VBA as the solution.

[Damian37]

I was merely suggesting that rather than loop through individual records one at a time to incorporate them in a user form that you first grab all the data and if necessary load the lot in one go.

I would however question whether it's more efficient to use a user form or just control the way the user edits the relevant rows on the data sheet itself.
I tend to prefer using standard Excel functionality in the first instance if it's reasonable and acceptable to do so rather than starting with VBA as the solution.

Hi Richard,
I see what you're saying, unfortunately we're dealing with users that have a tendency to go in an make changes to fields they aren't supposed to make changes to. Therefore, we are trying to make it so they are forced to use a userform giving us the ability to hide the data tab, thus preventing them from making any unnecessary changes to the raw data being entered in.