# Changing Cost Allocation Macro?

May 23, 2012

I am trying to write a Macro to allocate a certain cost across thousands of orders. The number of orders will obviously change monthly. The cost is relatively small, and when divided evenly by the total orders needs to be rounded up to the nearest cent (Typically \$.01 - \$.02)

I then need the cost to be insered in a colum running next to a total cost column, but I need it to stop when it reaches the total cost and just start adding zeros to the rest of the orders so as not to overcharge.

Not sure if I need to incorporate solver somehow. I figured I would reference the allocated cost from a cell in the worksheet but not sure how to insert any algebraic logic into a macro.

## VBA Macro To Create Multiple Workbooks From One Master Workbook By Cost Centre

Jun 27, 2013

VBA Macro to work through a worksheet that consist of static data (tab 4) cost centres and to populate a new work book per cost centre consisting of three tabs for every cost centre found in the static data.

The master Workbook has the following tabs:

Tab 1 is called travel and consist of column a which is the cost centre number (plus 14 other columns)

Tab 2 is called Mobile and consist of column a which is the cost centre number (plus 14 other columns)

Tab 3 is called Expenses. and consist of column a which is the cost centre number (plus 14 other columns)

Tab 4 Static Date column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns).

If no information found on a specific cost centre, the tab will include the headers and return the words "No transactions for this period"

Whilst splitting data into Tabs the workbooks should check against the Static Data table and include cost center description in Column B of each tab in the new workbook.

If master workbook consist of the following....

Tab 1 is called travel and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,557,

Tab 2 is called Mobile column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns) cost centres, 555, 78689,

Tab 3 is called Expenses. column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,

tab 4 Static Date - column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns). cost centres, 555, 557,78689

It should output 3 workbooks by cost centre number.

One for 555, which consists of 3 tabs, travel, mobile and expenses.

A second for 557 which has 3 tabs travel, mobile and expenses, but only with data in the travel tab.

A third for 78689 which has 3 tabs travel, mobile and expenses, but only with data for mobile data.

The workbooks will be replicates of the contents within the tabs where column a wil be the cost centre plus 14 additional columns.

## Excel Macro Or VBA To Calculate Desired Profit Margin Depending On Cost

Sep 12, 2013

I need to develope a macro for Excel to calculate desired profit margin depending on our cost of each item. Here's the scenario.

A1: landing cost
If 0

## Iteration Inconsistency: Allow For A Cost Being Added To Loan Amount Where The Cost Is Based On The Total Loan Amount

Mar 15, 2007

In a financial environment we have a calculator which uses iteration to allow for a cost being added to loan amount where the cost is based on the total loan amount. Iteration is set to 100 iterations with max change .001

On one PC the first time the calculator is opened it gives a particular (incorrect) result. If the input cells are cleared and the data re-entered, it gives the correct result. This only happens on one particular PC. Is there some other setting , other than the iteration setting, that would cause this?

## VBA For Stock Allocation

Jun 13, 2008

Let say I have 5 shops: A, B, C, D, and E

They request for stock replenishment from warehouse as follows:
A: 4 pieces
B:2 pieces
C:3 pieces
D:3 piece
E:3 piece

But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock

how to write the scripts to do this allocation.

## Auto Allocation Of Data

Jun 29, 2013

I have a list of 500 'cases' for staff to work on and I want to auto allocate them to people based on their skills. Whilst I can do this easily with each person have their own page in my workbook, I'm having the issue of the same data being replicated onto multiple sheets.

The main data is on one page and each person has their own individual page for their cases for the day. Is there any way to avoid multiples being sent out whilst creating a kind of 'priority ladder' as such?

## Allocation Of Permutations In Excel

Nov 26, 2013

I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:

1: Red_S1
2: Red_S2
3: Red_S3
4: Red_S4
5: Green_S1
6: Green_S2
7: Green_S3
8: Green_S4
9: Blue_S1
10: Blue_S2
11: Blue_S3
12: Blue_S4
13: Yellow_S1
14: Yellow_S2
15: Yellow_S3
16: Yellow_S4

I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).

If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?

## Random Distribution/Allocation

Nov 1, 2009

I have a spreadsheet which has a list of staff (column B) and work categories (row 3).

Row 4 is updated each day with a count of the incoming work for each work category.

I want to be able to randomly distribute (as evenly as possible) the no of work items in Row 4 between the doers (B7:B13). Each item of work also has to be checked so I would also like to randomly distribute the checking between the checkers (B16:B18)....

## Time Allocation Calendar

Jul 12, 2007

I am creating a time allocation / holiday tracking calendar. 150 employes (1 per column) and then using rows (10 - 375) as a daily track. I am having trouble with two aspects and would value your assistance.

1. i have locked the top 9 rows and would like row10 to jump automatically to todays date. i.e current info directly below the titles block

2. How do i make the calendar wrap around i.e. if someone puts in for a week holiday starting dec 31st 2007, how do i make the calendar roll round to 2008.

## Coming Up With Inventory Allocation Formula

Aug 4, 2014

I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column.

The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:

1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain.
2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab.
3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.

Inventory Allocation.xlsx

## Formula For Batch Allocation Or Split Up?

Oct 2, 2013

I need a formula for batch allocation automatically.

See the attached file : Batch_Allocation.xlsxâ€Ž

## Fill Vehicle Allocation Table

Nov 26, 2006

I am reposting this as my previous post was deleted.. i dont know the real reason for the same...

The raw data in the attachment contains:

•C1 to AI1 refers to vehicle numbers.
•Column A refers to Shift Time
•Column B refers to Number of vehicles required in that particulars Shift time

The output:

•As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time

•The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value.

•Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs).

• all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated

## Allocation By Daterange - Single Cell

Apr 27, 2007

I m creating a single cell formula in 'report'B3 that would allocate 'data'D3 appropriately by month heading if copied across. A few other threads on this topic only acheive this by creating an addtional row, not in a single cell. Since I'm going to use this on a report in Access, I just need the calculation. Is it not possible to acheive this in a single cell?

## Revenue Allocation Using Combined/Nested IF Statements

Jul 24, 2008

I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:

where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"

I need all of the above in formula line which will be inserted in the revenue source column

I have attached the file with a sample of the information I am using.

## Summarise Data After Applying Allocation Split

Feb 25, 2009

The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).

## VLookup With 2 Criteria - Provide Log Of Staff Allocation

Aug 31, 2012

I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.

Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B\$3,David!\$A:\$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.

The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.

I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.

## Input Allocation Percentage Dependent On Number Of Variables

May 15, 2014

On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:

Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)

The allocation percentage is based on the below; anything outside of this should result in "Other".

Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%

Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%

Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%

Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%

## Excel 2010 :: Fails To Release Memory Allocation

Jan 27, 2014

I am running Excel 2010 32bit under Windows 7. The Excel file worked perfectly under Excel 2007 and Windows XP. The Excel file is somewhat complex with numerous links and embedded cell formulas. Occasionally, I get the following error message:

5.jpg

Before I install the 64 bit version, I attempted to chack a few other things. I opened the task manager and while viewing the process tab, I opened my Excel file. Once the file loaded, this is the allocation displayed in task manager:

1.jpg

This seemed reasonable considering the size of the Excel file (33Mb). Without changing anything, I clicked save to make sure it was working properly. While viewing the process, I watched the allocation climb, substantially. when it completed, this is what the process displayed:

2.jpg

Continuing to click the save button, without changing anything, continued to increase the memory allocation until the error message was displayed:

3.jpg

4.jpg

Closing Excel and re-openeing the file resets the memory allocation back to normal, which is a popular solution to this issue in the forums.

I did check the same issue using Excel 2007 and the memory allocation releases after each save.

I can see how installing the 64 bit version of Excel can correct this issue, and with 12Gb of Ram, I will probably use this method until I can get this fixed.

## Changing UDF To Macro

Feb 8, 2013

I found the following function in an old thread.

Public Function cleanString(fNameStr As String)Dim i As IntegerConst BadChars = "=+/'*?).,#%~!(\$[]Ã·â„¢Â©" For i = 1 To Len(BadChars) fNameStr = Application.WorksheetFunction.Substitute(fNameStr, _ Mid(BadChars, i, 1), "") Next i cleanString = fNameStr
End Function

How would this be modified to work as a macro on selected cells (with selection?) I lost the formatting

## Changing A Macro

Jan 6, 2009

what needs to be changed on this code to only apply once for the row and not over and over again.

Dim Cell As Range
If Application.Intersect(Target, Range("ActivityRange")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Cell In Range("ActivityRange")
If Len(Cell.Value) > 0 Then Cell.Value = Cell.Value & Chr(10)
Next Cell
Application.EnableEvents = True

## Using Same Macro To Sum Changing Ranges

Jul 21, 2014

Is it possible to use the same macro when ranges have been changed? Attached is a sheet that I update frequently (rows will change day to day, not columns) and the goal is to have a dynamic macro that sums the rows highlighted in yellow and red. Is this possible?,

## VBA Macro (Changing Cells From A1-C3 Then A2-C2)

Aug 7, 2014

What my code needs to do: It needs to verify data in column rows say for this example columns A to C and infinite rows. It needs to verify the data in this order A1, B1, C1, A2, B2, C3 etc. (One of the part I have problems to do) It also needs to add to a row/cell "1" when certain conditions. (In the same line as the last checked data in the column "D" say for exemple last data that was checked is "A3" well the 1 must be written in "D3". The conditions are simple the first Data the macro encounters must be higher than 0.25 and all following data must be higher than 6, whenever the next data is lower than 6 or empty add 1 to the corresponding D row and restart at the 0.25 cell "checking".

## Changing Macro To Use A Loop

Mar 12, 2014

I'm using a macro to drag down a formula across a worksheet then again further down the worksheet which is working ok but I know there has to be a better way of doing it. At the moment every time I add a new column I have to edit the macro over and over and its getting out of hand. I know there must be a way of rewriting the macro into a loop but my skills are obviously still new.

Here's the macro:

[Code] ......

This continues over to column AO so far then I drop down a few rows and do it again:

[Code] .....

I think I need to set variables that set the row and column each time. maybe a "drag formula one column at a time from row x to row y until column header is empty then move to the new row and repeat"?

## Run Macro If Values Are Changing?

Dec 21, 2011

I have a code that clears the content of a cell if the value of that cell exceeds two.

Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Cells(1, 1).Value > 2 Then Cells(1, 1).ClearContents
End Sub

My problem is the value of this cell comes from a function and changes every second automatically (linked to a data feed). So I'm not changing it manually, therefore my code is not working unless I touch it. I just wanted to ask if I can add a code onto this which checks the values and runs the ClearContents macro if there is any change.. I've got a few sheets like this so I need this to run in the background..

## Changing VBA Macro Using InStr?

Aug 29, 2012

I want a vba macro which performs some thing like this. I think probably this can be done by using InStr function, the macro attached here just find the exact same value and shows the result but i want similar values,

Code:
Sub a()
lastrow = 10
For arow = 11 To 16

[Code].....

## Changing Ranges In A Macro?

Jun 24, 2013

Code:

Sub appointment_nieuw()
With CreateObject("Outlook.Application").CreateItem(1)
.Subject = Range("C3")
.Start = DateValue(Range("A3")) + TimeValue(Range("B3"))
.Duration = 0
.Location = Range("D3")
Dim cell As Range
For Each cell In Range("F2:H2")
.body = .body & Space(2) & cell
Next
.Save
End With
End Sub

I have this macro that works great, but every time I have a different appointment. I have to go into VB editor and change the ranges for the subject, datevalue, timevalue, location and body.

Can I get a text box (or something better) to pop up and I enter the ranges for the 5 in it and have it change in the macro?

## Macro Not Changing Cells

Dec 20, 2006

I have button in the Excel sheet.When I click the button the following code got triggered.

Sub Button1_Click() 'YieldCurveGet
Dim arrayData As String
Dim CurveString As String
'arrayData = "Test,Test1....." '(for eg)
arrayData = WorksheetFunction.RTD("OrcExcel.RTDServer", "", "YieldCurveGet", "AUD yield curve", "AUD", Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null)
CurveString = Split(arrayData, ",")
i = -1
For x = 1 To (UBound(CurveString) / 2)
i = i + 1
range("C" & x) = CurveString(i)
i = i + 1
range("D" & x) = CurveString(i)
Next x
End Sub

After triggered get invoked I need to set value of "arrayData " from VBA to Excel.Iam not sure what's going wrong.I cannot see the value in the Excel.

## How To Create A Word Changing Macro

May 22, 2014

Is there any way to create a Macro that is any cell in column D has the word Shop it would change the adjacent word in column E from Roof Time to Shop Time?

## Name Manager Changing Range After Run Macro

Mar 6, 2014

I have a workbook that has a saved range. I created it with the Name Manager. See below:

[Code]....

When it looks like that the VBA works great and creates my pivots. But after I run my first macro to mess format my source data the range ends up looking like below:

[Code] .......

It is for some reason changing the "A" to "Y".

How I can get it to stay with the original data?

## Runing A Macro On By Changing A Value Of The Cell

Sep 11, 2008

Is there a way to change the value of cell A1, for example, from one value to another, hit the enter key, and activate a macro this way.

A couple of qualifications:
- the value in cell A1 will never be the same (that is it could be -23.43 or .12)
- I can not simply tie the macro to the "enter" key as there will be numerous cells (A1, B3, C90 and so on) in the same sheet that I will need to do this with, each tied to a different macro.