• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

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)
0
spudmcc
Asked:
spudmcc
  • 5
  • 5
1 Solution
 
Harry LeeCommented:
spudmcc,

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?

Sub Spread()
'
'Initialize Variables
iRow = 4 'This is the ROW you want to spread across
d = InputBox("Please Enter The Number of Days You Need to Spread")
x = Cells(4, 7) 'Put in the AMOUNT here
'
'Get the nearest integer to spread across all the days
iNearest = Int(x / d)
For iCol = 68 To d + 67
   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 + 67) = iNearest
   Cells(iRow, fRandom + 67) = Cells(iRow, fRandom + 67) + 1
Next i
End Sub

Open in new window

0
 
Harry LeeCommented:
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?
0
 
spudmccAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
spudmccAuthor Commented:
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.
0
 
Harry LeeCommented:
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.
0
 
spudmccAuthor Commented:
Here is an example.
example.xls
0
 
spudmccAuthor Commented:
You were right about the placement of the data....my mistake.  Sorry.
0
 
Harry LeeCommented:
spudmcc,

this should do the trick for you.

Sub Spread()
'
'Initialize Variables
d = InputBox("Please Enter The Number of Days You Need to Spread")
RwCnt = Cells(Rows.Count, 7).End(xlUp).Row
For iRow = 3 To RwCnt 'This is the ROWs you want to spread across
'    iRow = 4
    x = Cells(iRow, 7) 'Put in the AMOUNT here
'
'Get the nearest integer to spread across all the days
    iNearest = Int(x / d)
    For iCol = 68 To d + 67
    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 + 67) = iNearest
    Cells(iRow, fRandom + 67) = Cells(iRow, fRandom + 67) + 1
    Next i
Next iRow
End Sub

Open in new window

0
 
spudmccAuthor Commented:
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
0
 
Harry LeeCommented:
I'm glad I can help
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now