Solved

Help with modifying VBA code

Posted on 2014-02-13
10
333 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:spudmcc
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:spudmcc
Comment Utility
Here is an example.
example.xls
0
 

Author Comment

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

Accepted Solution

by:
Harry Lee earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I'm glad I can help
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now