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.