Incremental Loop

[Opv]

Is there a way to create a loop that takes only one step at a time and then waits for another trigger to proceed to the next increment in the loop? For example:

Range("$A4:$A50")

If I want to loop through the above range one cell at a time, do some stuff with the value in cell A4, then when I get ready to do stuff with the value in cell A5, I could trigger the loop to proceed to getting the value from that cell, etc., etc., until I get to cell A50, then start over at A4.

[austenr]

Lots of kinds of loops. For Next, Do While, Do Until, need more info.

[mdmackillop]

Try this userform solution

[mdmackillop]

An alternative allowing Range selection

[Opv]

Try this userform solution

Thanks. I learned several things from your suggestion. It dawned on me as I was stepping through the suggested process that perhaps a loop is not the most efficient way of accomplishing my goal. I think I'll explore the use of Find as an alternative.

Thanks again. I appreciate the help!

Opv

[Opv]

OK. I've attached a sheet demonstrating the approach I've decided to take. What I've done is crate a numerical index for the terms/phrases in the database. I then created a corresponding Practice sheet.

In the Practice sheet, I first populated columns A and B so that I could randomize the index numbers in the database. Col A contains Rand() in each cell. Col B employs use of both the Index and Rank functions to effectively rank the terms/phrases so that I effectively get an non-duplicated array of numbers from the database Index in a random order.

Next, I created a series of subroutines. Randomize() repopulates the random numbers in Col A, which in terns recalculates the rank order in Col B. FindNext() is SUPPOSE to start with the first number in Range("B4"), retrieve the term from the database with the corresponding Index number and display the Greek term, the English term and a practice field. Once I correctly type in the word, I can click Find Next and it is suppose to go to the next Ranked number (i.e., Range("B5"), retrieve the next entry from the database sheet and display it for practice, etc., etc.

My problem is that something is not working right in my FindNext() routine. It is suppose to go through the rows one at a time and when it reaches the last row, start over with Row 4, at which time I can re-randomize the rankings and practice the terms in a different order. I have a field at the top of the Practice sheet that displays how many terms are remaining in the cycle. It should decrease incrementally from 141 to 140 to 139, etc., down to 1. That lets me know when to re-randomize. However, it is jumping around and is inconsistent. I have stared at it until the screen is blurry. I finally decided to post it and see if someone can help me figure out why it's not working properly.

Thanks,

Opv

[Opv]

I thought I had figured out my problem but it's still not proceeding through the entries properly. I welcome any suggestions for how to correct this problem.

Thanks,

Opv

[mdmackillop]

I've simplified the steps required in this example to add the data in a similar fashion. Ranges are all dynamic, so you should be able to add more terms to you database without having to modify the code.

Another approach I considered was to use VLOOKUP to fill all the data at the start, but set the Font to white. You could then change the Font colour of each item in turn.

[Opv]

I've simplified the steps required in this example to add the data in a similar fashion. Ranges are all dynamic, so you should be able to add more terms to you database without having to modify the code.

Another approach I considered was to use VLOOKUP to fill all the data at the start, but set the Font to white. You could then change the Font colour of each item in turn.

Thanks, I'll check it out ASAP. As far as the colors, that's no big deal. The red is the result of conditional formatting and only shows up if I type in the Greek term incorrectly. I'll check out the changes and let you know shortly. Thanks again,

Opv

[Opv]

I've simplified the steps required in this example to add the data in a similar fashion. Ranges are all dynamic, so you should be able to add more terms to you database without having to modify the code.

Another approach I considered was to use VLOOKUP to fill all the data at the start, but set the Font to white. You could then change the Font colour of each item in turn.

I must have explained my dilemma incorrectly. I am not really wanting to show the rows from the database one row at a time, ending up with the same number of rows on the practice sheet as there are on the Database. I am only wanting one fixed line of practice data which changes each time I click the FindNext button.

[mdmackillop]

No problem

[Opv]

No problem

I am receiving a run time error once it gets down to 10 items left.

Another thing I noticed is the Ranking number for the word displayed doesn't match the number in A2. Is there a change to the way the script cycles through the ranked items?

[mdmackillop]

Note that it finds the bottom index first.

[Opv]

Note that it finds the bottom index first.

Looks great. Thanks! Is it still configured to view the database as a dynamic range so that it works as I add new terms/phrases?

[Opv]

Looks great. Thanks! Is it still configured to view the database as a dynamic range so that it works as I add new terms/phrases?

Never mind. I see that the range is indeed dynamic. Love it. I'll enjoy practicing more now. Thanks for all of your help.

Opv

[Opv]

mdmackillop, FYI, I added a timer to the script so that if I spell the term correctly, the findNext subroutine is called automatically after 4 seconds. That gives me time to say the word after I type it and then have the next word appear without having to take my hands off the keyboard. The timer stops working when the count reaches 0 so I when I'm ready to re-randomize I can do that. Heck, I may even automate that eventually. :)

Thanks again for all your help.

Opv

[Opv]

Note that it finds the bottom index first.

How difficult would it be to modify the various scripts (and the defined range name) to cause the practice sheet to read and cycle through only the visible rows in the database in the event Auto Filter is being used? As I add new words to the database, I'm finding more of a need to practice them than the earliest words with which I am very familiar. (I have a column in the database that I've included an X in the cells for the words that need the most practice.) That way I could practice just those words/phrases and then I could unfilter the database if I wanted to rehearse the entire database.


Just curious,

Opv

[Opv]

How difficult would it be to modify the various scripts (and the defined range name) to cause the practice sheet to read and cycle through only the visible rows in the database in the event Auto Filter is being used? As I add new words to the database, I'm finding more of a need to practice them than the earliest words with which I am very familiar. (I have a column in the database that I've included an X in the cells for the words that need the most practice.) That way I could practice just those words/phrases and then I could unfilter the database if I wanted to rehearse the entire database.


Just curious,

Opv

P.S. Just for clarification, I'm not asking that the Auto Filter process be automated. I am intending to do that manually as needed. I'm only wanting the scripts to function with a filter applied (only on visible words/phrases) just as they do when no filter is applied.

Thanks,

Opv

[mdmackillop]

If this is a teaching procedure, I would consider a routine which monitors response time and factors this into presented training phrases.

[Opv]

If this is a teaching procedure, I would consider a routine which monitors response time and factors this into presented traing phrases.

Hmmmm. I hadn't thought about that. Not sure I require that much functionality (unless it can be easily incorporated as an ancillary component). This spreadsheet just serves as a dynamic set of flashcards that facilitates reviewing, speaking and writing (typing) Greek words one at a time. I'd be in hog heaven just to have the ability for the current functionality to work when the database is filtered. Anything beyond that would be an extra dessert. :)

Opv

[Opv]

If this is a teaching procedure, I would consider a routine which monitors response time and factors this into presented traing phrases.

Should I move this additional piece of functionality to a new thread since the original functionality associated with the original request is already solved?

[mdmackillop]

Do that. I'll look at it there, and you may get fresh insights.

[Opv]

Do that. I'll look at it there, and you may get fresh insights.

In case you missed my new thread, it is "Working with a Filtered Dataset." Thanks to your help and suggestions, I was able to come up with a way to make my Greek dictionary work when it's in filtered mode. I posted the modified test workbook in the above referenced thread in case you want to check it out.

Thanks for your help,

Opv