The Piecemeal Man (abmann) wrote,
The Piecemeal Man
abmann

  • Mood:

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.
Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 6 comments