Solved

Help with modifying VBA code

Posted on 2014-02-13
10
346 Views
Last Modified: 2014-02-13
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
Comment
Question by:spudmcc
  • 5
  • 5
10 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39856923
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39856933
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
 

Author Comment

by:spudmcc
ID: 39856965
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:spudmcc
ID: 39856981
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39856993
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
 

Author Comment

by:spudmcc
ID: 39857030
Here is an example.
example.xls
0
 

Author Comment

by:spudmcc
ID: 39857039
You were right about the placement of the data....my mistake.  Sorry.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39857277
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
 

Author Closing Comment

by:spudmcc
ID: 39857512
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
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39857595
I'm glad I can help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question