Help with modifying VBA code
Posted on 2014-02-13
Need some help in modifying this VBA code. I inherited it and have "zero" knowledge of VBA so I once again need to rely on your patience and knowledge.
Here is the code. Below the code is what I would like to change.
iRow = 4 'This is the ROW you want to spread across
d = Cells(1, 5) 'Put in the number of DAYS here
x = Cells(2, 5) 'Put in the AMOUNT here
'Get the nearest integer to spread across all the days
iNearest = Int(x / d)
For iCol = 6 To d + 5
Cells(iRow, iCol).Value = iNearest
'Now use a random number generator to get rid of the "leftovers"
iLeftOver = x - d * iNearest
For i = 1 To iLeftOver
' Make sure the same cell doesn't get hit twice
fRandom = Int((d * Rnd(i)) + 1)
Loop Until Cells(iRow, fRandom + 5) = iNearest
Cells(iRow, fRandom + 5) = Cells(iRow, fRandom + 5) + 1
Items to change:
1---I would like to be able to enter the days required instead of having it in a cell.
2---The number(s) are in column g and not just in one specific cell but in a range of cells. I want to it to "spread" the data beginning in column "BP" and whatever row the original data lived in. For example if 1543 was in "G4" and the number of days entered were 23 then the data would be spread from BP4 thru CL4. This way I can use this routine on a range of data instead of just one cell at a time.
I hope this makes sense. Once again, I am thanking all in advance!