Microsoft Office Tutorials and References
In Depth Information
‘as a starting point you need to know the next highest Index position
‘from whatever worksheet is active at the time.
intWS = ActiveSheet.Index + 1
‘If you are on the last worksheet, you’ll have reached the end of the line,
‘so define the intWS as the first Index worksheet.
If intWS>Worksheets.Count Then intWS = 1
‘Open a Do Until loop that determines the next Index number,
‘only considering visible worksheets.
Do Until Worksheets(intWS).Visible = True
‘Add a 1 to the intWS variable as you iterate to the next highest Index number.
intWS = intWS + 1
If it turns out that the intWS Index variable reaches a number
‘that is greater than the count of worksheets in the workbook,
‘the intWS number is set back to 1, which is the first Index position.If intWS >
Worksheets.Count Then intWS = 1
‘Loop to start evaluation again, until the proper Index number is found.
‘Select the worksheet whose Index property matches the index number
‘that has met all the criteria.
For another example, suppose you want to update your AutoCorrect list easily and quickly. Say
you have a two-column table on your worksheet that occupies columns A and B. In column A,
you have listed frequently misspelled words, and in column B are the corrected words that you
want Excel to automatically display if you misspell any of those words. For example, in cell A1
you have entered “teh” (without the quotes) and in cell B1 you have entered the correction of
“the”(without the quotes). This macro, using a Do…Until loop, will handle each entry in column
A and continue to do so until the first empty cell is encountered, indicating the end of the list.
‘Declare a Long type variable to help looping through rows
‘of the two-column list.Dim i As Long
‘Declare two String type variables:
‘one for thr original entry, and the other for the text string replacement.
Dim myMistake As String, myCorrection As String
‘Establish the number 1 for the Long Variable, representing row 1
‘which is the first row in the example list.
i = 1
‘Open a Do Until loop, telling VBA to stop looping when an empty cell
‘is encountered in column A, indicating the end of the list.
Do Until IsEmpty(Cells(i, 1))
‘Define the myMistake variable as the text contents of the cell in column
A.myMistake = Cells(i, 1).Value
‘Define the myCorrection variable as the text contents of the cell in column B.
myCorrection = Cells(i, 2).Value
‘VBA tells the Excel Application’s AutoCorrect property to update itself with
‘the two strings from columns A and B.
Application.AutoCorrect.AddReplacement What:=myMistake, Replacement:=myCorrection
‘Add a 1 to the i variable in preparation for evaluating the next row in the list.
i = i + 1
‘The Loop statement starts the process again for the next row in the list.