For this assignment, we are going to step away from the engineering applications and have a little fun. The following spreadsheet is designed to translate normal English sentences into redneck dialect:
The first sheet of this workbook (shown above) is called Main. There is a second sheet called Index that looks like this (this is a partial view of the list):
Click here to download a partially completed copy of this spreadsheet. Then do the following:
Write code associated with the Fix 'Er Up button that translates each of the sentences in column A of the Main sheet from normal English to redneck, and keep in mind that you need to specify which worksheet your code is working on. To do this, you will need to write an outer loop that starts at row 5 of the Main sheet and traverses through each row of the table until it reaches an empty row. Each time through the loop you should copy the sentence in column A into a string variable. Then you will start an inner loop (nested inside your main loop), that starts at row 4 on the Index sheet and loops over each of the rows in the Index table until it reaches an empty row. On each row, you should use the Replace function to substitute the word in column B for the word in column A of the Index table in the current sentence. Make sure your calls to the Replace function are cumulative. I.e., any change you make to the sentence is additive ("mystr = Replace(mystr, ..."). Once you have gone through the entire list, write the resulting translated sentence to column B of the main sheet and go to the next sentence.
Test your code to make sure it works. You may want to add your own substitutions to experiment with the translator. Have fun!
1. Be sure to save the changes to your spreadsheet.
2. Upload your spreadsheet via Learning Suite.