?

Log in

No account? Create an account
entries friends calendar profile ABMann.net Previous Previous Next Next
Excel + VisualBasic = OW - Portrait of a Young Man as The Artist — LiveJournal
abmann
abmann
Excel + VisualBasic = OW
It took six hours to come up with this:
Dim CurrentRow As Integer
Dim CurrentCol As Integer


Range("J11:AF28").Cells.FormulaR1C1 = "=ROUNDUP(RAND()*100,0)"


For Each cell In Sheet1.Range("J11:AF28")
    CurrentRow = ActiveCell.Row
    CurrentCol = ActiveCell.Column
    If cell.Value >= 97 And (Cells(cell.Row, 4).Value = "Remote" Or 
;   Cells(cell.Row, 5).Value = "Remote" Or Cells(cell.Row, 6).Value = "Remote") Then
        cell.FormulaR1C1 = "Y"
    ElseIf cell.Value >= 93 Then
        cell.FormulaR1C1 = "X"
    Else
        cell.FormulaR1C1 = ""
    End If
Next cell

Insane. However, this is the most efficient way to do what I want to do. Originally I was going to randomly assign values from list A to list B until List B was full. This got crazy when I had to build arrays and increment ActiveCell position in Excel. I realized that Excel is essentially a multidimensional array that I could play with straight out. From there I just need to enter a random number into a range of cells and then loop over those numbers and conditionally assign Xs, Ys and null values.

My breakthrough came when I realized that I could create and increment an arbitrary counter in a For loop that would go through every single cell in a range. That made it far, far, far faster than the crazy-ass, clunk code I was trying to write Tuesday night.

I inadvertently spent the last 4 hours on this. Coding is fun for me. I just wish I were better at it so it didn't take so long.

6 hours for 14 lines of code.
--
And I've totally forgotten to eat all this time. Oops.

Current Mood: accomplished accomplished

6 comments or Leave a comment
Comments
tandu From: tandu Date: April 12th, 2007 04:58 pm (UTC) (Link)
I spent 2 days tracking down the fact that I had missed a 'commit' statement in my save to database function.

I've got 10 years doing this stuff. It happens.
abmann From: abmann Date: April 12th, 2007 05:21 pm (UTC) (Link)
Must. Be. Perfect!

I'm only upset because I was neglecting other things that were somewhat more important. I could have actually done what I was intending for this code manually a few times in the time it took me to code it. Also, I enjoy it and enjoying can't be work. Therefore, I was slacking.

I know. I'm crazy.

I never got your phone number. I'll pay you when the hard drive comes in? Maybe get together for coffee one night next week with the ladies?
abmann From: abmann Date: April 12th, 2007 05:42 pm (UTC) (Link)
I delorted your number. Mine. All mine!
techdragon From: techdragon Date: April 12th, 2007 05:30 pm (UTC) (Link)
Dude! You just murdered my friends' page with the code length!
abmann From: abmann Date: April 12th, 2007 05:39 pm (UTC) (Link)
It had it comin'
questingfalcon From: questingfalcon Date: April 12th, 2007 06:00 pm (UTC) (Link)
Its worse if you spend 6 hours coding 14 lines, 4 hours recoding them to handle a different validation, and 15 minutes the next day implementing a different approach that works sexy good.

The Indian Code Monkey says Hi.
6 comments or Leave a comment