When I first played with Excel in High School, I noticed an interesting little code feature tucked away in the macro section. People with experience in Basic programming can set up basic codes to perform macros and automated functions. Having at the time just completed a Visual Basic class I considered this to be a valuable tool to explore later on.
Then eleven years passed…
…and fast forward to last week when I found myself needing to perform a big spreadsheet edit. Put simply, for a column in a list of product rows I needed to insert a “Y” if the product in the row was over a certain dollar amount, and a “N” if it was less. Performing this task manually was a problem considering the spreadsheet consisted of over 16,000 rows.
After consulting the great and mighty Google I failed to find an obvious solution. Fortunately one tech help page suggested to someone with a similar problem to write a macro. I realized I could do something similar to automate the process.
Although I never got past basic Java (recursion killed me) before committing myself to the humanities, I remembered enough to come up with a basic If-Then statement which, after some tweaking, solved the problem nicely. Trying to remember Basic programming took some time, but it was much faster then trying to manually edit the spreadsheet myself.
Moral of the story? Having the “tech” in Technical Writer pays off.
For the record, the code I wrote is below. I’m pretty sure it was more complicated then necessary, but it worked!
Dim Cell As Object
For Each Cell In Sheets(“mdd1”).Range(“AN2:AN16860”)
If Cell.Value > 500 Then
Cell.Value = “y”
Cell.Value = “n”