Help with modifying VBA code
Hi Experts!
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.
Sub Spread()
'
'Initialize Variables
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
Next iCol
'
'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
Do
fRandom = Int((d * Rnd(i)) + 1)
Loop Until Cells(iRow, fRandom + 5) = iNearest
Cells(iRow, fRandom + 5) = Cells(iRow, fRandom + 5) + 1
Next i
End Sub
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!
spudmcc (Andy)
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.
Sub Spread()
'
'Initialize Variables
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
Next iCol
'
'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
Do
fRandom = Int((d * Rnd(i)) + 1)
Loop Until Cells(iRow, fRandom + 5) = iNearest
Cells(iRow, fRandom + 5) = Cells(iRow, fRandom + 5) + 1
Next i
End Sub
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!
spudmcc (Andy)
spudmcc,
If in Question #2, you mean you need to spread multiple rows of data, how many rows are there in Column G? Can you upload a sample?
If in Question #2, you mean you need to spread multiple rows of data, how many rows are there in Column G? Can you upload a sample?
ASKER
Let me see if I can shed some light on this a bit more. Currently the routine gets the days from cell E1 and the amount from E2. What it does is takes the amount in E2 and spreads it out over the number of days from E1. This all happens in row 4. Using my example from above it would take the total of 1543 and spread this amount over 23 days (taken from E1).
The whole idea is that I have one monthly total that needs to be spread out over the number of work days in a given month. The problem is that I have to have whole numbers and no decimals. So it takes the total and divides them out and randomly takes whats left and changes a couple of cells so that it totals the number in E2. It may add one or two or take away one or two randomly to get it to stay a whole number. This all works fine.
The issue is that this script was designed for 1 row of data at a time. I want to be able to have it look at all the values in column G and do its magic for all rows that are populated in column G. This way I can do an entire spreadsheet or 300-400 rows at a time all using the same number of days (this stays static for each workbook). So if I choose 23 it is always 23 for that workbook.
Hope this helps a bit.
The whole idea is that I have one monthly total that needs to be spread out over the number of work days in a given month. The problem is that I have to have whole numbers and no decimals. So it takes the total and divides them out and randomly takes whats left and changes a couple of cells so that it totals the number in E2. It may add one or two or take away one or two randomly to get it to stay a whole number. This all works fine.
The issue is that this script was designed for 1 row of data at a time. I want to be able to have it look at all the values in column G and do its magic for all rows that are populated in column G. This way I can do an entire spreadsheet or 300-400 rows at a time all using the same number of days (this stays static for each workbook). So if I choose 23 it is always 23 for that workbook.
Hope this helps a bit.
ASKER
Harry Lee
When I popped in your routine...the message box comes up but it always returns a zero. I don't think it is calculating anything.
When I popped in your routine...the message box comes up but it always returns a zero. I don't think it is calculating anything.
What do you mean it always return 0?
Do you mean the the 23 columns are all 0? Probably you do not have the number in G4 as you mentioned in your question.
I think I understand your problem now. I need a few more questions about Column G.
1) Where does the data starts in column G? which row it starts on?
2)Does column G has a total or subtotal line at the bottom after the numbers?
If it does, where is the subtotal? (how may rows after the numbers) for example, immediately after, or a blank row in between.
Do you mean the the 23 columns are all 0? Probably you do not have the number in G4 as you mentioned in your question.
I think I understand your problem now. I need a few more questions about Column G.
1) Where does the data starts in column G? which row it starts on?
2)Does column G has a total or subtotal line at the bottom after the numbers?
If it does, where is the subtotal? (how may rows after the numbers) for example, immediately after, or a blank row in between.
ASKER
Here is an example.
example.xls
example.xls
ASKER
You were right about the placement of the data....my mistake. Sorry.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are my hero! Thank you so much for your patience and your excellent solution. This is going to save us a huge amount of time each month! I can't tell you how much I appreciate your help!
Have a super day and thank you again!
Andy
Have a super day and thank you again!
Andy
I'm glad I can help
This will for sure takes care of the Question #1 but I don't really get what you mean in Question #2
Do you mean you need to spread multiple rows of numbers? Or do you mean you need to spread the sum of the numbers in column G onto row 4?
Open in new window