Running Calculation X Times With Changing Inputs And Placing Of Output Into Cells
Jan 28, 2014
I have a cell in sheet1 (say, B2) in which I can key a date. Once I change that date, cell C10 updates (after I hit F9 to recalculate) with a new number based on the new date.
I have a list of dates on another sheet, from 1/1/2009 to 12/31/2013, all in column B. I want to produce VBA code that will run through each day, 1/1/2009 to 12/31/2009, in sheet1 cell B2 and take the output in C10 and place it next to each day in column B until it reaches the end (12/31/2013). I know this is possible but for some reason am having trouble coming up with the right looping mechanism.
You can imagine if I had to manually key in each day to get the desired output how tedious that would be.
I have a calendar set up with dates going along P6:CU6. Column C9:C27 has various status' for the job such as "Complete" or "In Progress" etc. Next to that in rows D and E I have start and end dates for the job. What I am trying to do is change the fill colour of the empty cells that are in line with each job, so that between the correct dates they are the corresponding colour to the status. So a complete job on row 9 that started on 26/05/14 and finished on 28/05/14 would have three green cells along Q9, R9, and S9.
I have tried an IF/AND/OR statement which looks like this:
I am getting a #VALUE! data type error, I have tried reformatting the cells to various date formats but no luck so far. This could be because my dates are set up as =P6+1 etc. but I am not sure. The error only occurs when the cell falls within the dates, otherwise it remains blank.
I intend to hide the value in the cell and use conditional formatting to change the cell colour based on the hidden input.
Once this is working I also need to take weekends into account, column G has any 2 value combination of Y and N to say whether the employee is working Saturday, Sunday or both. Row 7 has a "1" value underneath every Saturday and a "2" underneath every Sunday. I can imagine how this would work but combining it with the above formula could be complicated.
Now I would like to keep value of A3 in cell A7 every time I change input values in A1 and A2 and so on.
A1 = 15 A2 = 8 A3 = 15+8 = 23 A7 = 12
And when an input in A3 > 100, it will popup a message, that a total cannot over 100. My code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 3 Then
Range("A7").Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("A3").Value = Range("A7").Value Exit Sub Else If Range("A3").Value > 100 Then MsgBox "Total exceed 100. Check you inputs!" End If End Sub
I'm trying to take 5 inputs that are all in text format and inputs have fixed options what i need is a macro that will take them and turn them in to the relevant outputs. there is a select amount of combinations and i've been asked to do each combination as a separate macro or sub macro.
What I have so far is;
If C6 = "Panel plate" And D6 = "24 by 21" And E6 = "<1.59" And F6 = "N/A " Then I6 = "120" And j6 = "4" And L6 = "120" End Sub
A B C D E F G H I J K L 1 2 4 5 6 Panel plate 24 by 21 <1.59 N/A 7 8 9 10
(Doesn't seem to work)...
If those four cell match up then i need I6-L6 to line up with whats required. If they don't equal that i need it to stay clear.
I also need to add the same macro with different options.
VBA code that would update a chart automatically as you change the inputs which are located in cells in excel. The inputs are located in cells B5:B9 and i want a code so that as you change these numbers the chart would automatically update.
my sumproduct function is adding up all items that fall on that date which is fine, but i would love to calculate an average so divide by the number of items that were used in the calculation, is that possible?
What I am trying to do is get the ROW 13 and ROW 18 outputs depending on inputs in ROW 2 to 5. Inputs in ROW 2 to 5 can have any value in any year. I would like to perform the calculations if the value in column U is 2 (if 1 then no action required)
I don't need ROW 10, 11, 12 separately if that is easier ...I am interested in getting the ROW 13 basically.
Im measuring arrivals of buses during a two hour period, for example 10 til 12 Within in this time i have 12 buses.
The Problem
What i need to do is calculate how many buses have turned up in a "rolling hour". This means taking a sample of any 60 mins within the two hours and calculating how many buses turn up. There needs to be a min of 6 buses in that 60 mins
In theoy there would be 60 "rolling hours" within a two hour period.
For example first 60 min is 10 till 11, second 60 min is 10:01 till 11:01 and so on.
I know this is basic but I'm having a hard time here. I'm trying to insert certain data into a column of blank cells. I just need the fields to be on there once. As of right now it is pasting the first field multiple times.
Private Sub AA_Click()
If PS = True Then Range("A61:A70").SpecialCells(xlCellTypeBlanks) = "Pull Stations" On Error Goto 0 End If
If CS = True Then Range("A61:A70").SpecialCells(xlCellTypeBlanks) = "C-F-A Switch" On Error Goto 0 End If
I have a button that a user clicks to print the info they entered. I need some code to add a running count to a cell (say, H2) every time the user clicks that button and runs the print macro.
I work for a freight company and have been trying to create a macro for a spreadsheet that I have to create every single day. I pull a report from a program called the AS400 and it sends me an excel spreadsheet with information about certain shipments such as close time and arrive time (which are in military time such as 16:00) along with driver names.
Anyways what I need to accomplish is to highlight the entire rows font in red if the driver arrived an hour or more past there close time. When I get the spreadsheet the military times are not showing in the HH:MM format under format cells. So I select the HH:MM format and try to do a formula such as Arrive time cell (M1) - close time cell (L1) to get the time difference it shows as #VALUE!. I've tried to convert it to different time formats and nothing works. The values just stay the same and will not allow me to do calculations with them.
At first I thought this was because the program was sending those values as text. I tried to use the date>text to columns>finish button on the columns but did not work. I've tried to remove the":" then use the HH:MM cell format but still wont work. I've even tried a time value formula to try to convert the values such as =TIMEVALUE(LEFT(A1,2)&”:”&RIGHT(A1,2)).
But nothing works it will not allow me to add or subtract from the time values no matter what I do. I am running excel 2010 on Windows XP the english version.
when I run the code, because I have used many "Sheets("SheetNameHere").Select", the user will see the sheets flicking backward and forward many times and to avoid confusion, I don't want them to think "what the hell!"
Is there a way where I can still activate the sheets needed, but to somehow run all of "the code" in the background so all the user see's is maybe a status box or something which says "Please wait..."
I am encountering a problem with worksheets.copy that occurs after the copy method is called a certain amount of times. My issue is similar (exactly the same?) to what is described in these threads:
- Worksheet Copy: Method 'Copy' of Object 'Worksheet' Failed - Copy Method of Worksheet Class Failed in Loop Code
In my case, after running this code 40 times, it fails.
If loc_after Is Nothing Then .COPY after:=Worksheets(sht_name_orig) 'throws error here after being called 40 times Else .COPY after:=loc_after End If
I would like this code to be able to run more than 40 times. I have read Dave's suggested solution regarding creating a worksheet template: http://www.ozgrid.com/Excel/excel-wo...-templates.htm
However, in my situation the client has specifically asked that the report come as a single stand alone excel file with no add ins. In addition, I am copying several different worksheets, not just one single template that is being used over and over.
The formula works perfect - the only problem is the calc times can take 10+ minutes ( and longer with larger data sets ) because the data sets are so large. Is there another formula or way to get the same result that would speed the process up?
I'm trying to do what I thoughts was the simpliest thing, but for some reason can not manage.
I try to change to value of a checkbox, by a code, without re-running the CheckBox_Click () Macro. I have put a checkbox in a worksheet, and when tickmarking it, a msgbox will pop up (one msgbox for tickmark on), and you can press Yes or No. Pressing No, I will sett the Checkbox.value = False. The problem is, when I use this command, the checkbox_click macro will run all over again. Now, the checkbox is False, and antother msgbox will pop up (another msgbox for tickmark off). The results is then that I first get the msgbox I want, but it is followed by the msgbox for tickmark off, which should only pop up when the checkbox is ticked off manually.
Code is something like follows:
Private Sub CheckBox1_Click() Dim mySheet As Worksheet
I have some groups of data. Each group are 5 cells: ........
What I want is to make a formula to sum the five numbers of each group, then: =sum(a1:a5). but, how could I copy the formula to make Excel understand that I want the numbers from a6 to a 10, and from a11 to a16? I try to make it with left click in the square down at right, but it just add one value to each cell:
I have a small problem here with my worksheet. In my college i need to make a project about sales in a wooden floor shop.
I have done one part of work but actually i'm stuck at the specifications of my sheets.
What I need to do i added the screenshot with design Screenshot 2014-02-02 01.29.03.png
For my project i need to describe everything in my workbook as you can see in screenshot, i have made some notes but i'm note sure if its really right my teacher don't want or can't explain me well this part of work, i don't know why...
Now the question> i would ask some nice people if they can check my sheets and show me where i need to put inputs and outputs notes in the design sheet. Also in the screenshot I surrounded a part with red line there is a very strange thing no one know what is it 3 teacher telling different things.
how I can have a formula repeat down a column five times before it changes to another formula? For example. Say on tab 1 I have a list of products. On tab two I have five codes that repeat down the page over and over again. On tab two next to the repeating codes I need to repeat product one 5 times and then skip to product 2 on the 6th row and repeat five times and then skip to product 3 on the 11th row and repeat 5 times and so on?
Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost.
So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next.
But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed.
Let me try and simplify... 'Menu 1' has two option: 'A' and 'B'. If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'. If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'. Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'.... 'Menu 2' will still be displaying '1', even though the options are only '3' and '4'.
Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid
Need to run formula against the output of another formula, but excel is giving me an error because its seeing the formula not the output. Is there a way make excel run the formula against the output?
I know that I an probably asking for too much but I must get the formula some way. OK, I need a formula for this:
lets say I have 3 columns (A,B,C (search criteria). In each Column there is a DIFFERENT number. There is 3 more columns (D,E,F (used to compare).Each column has 3 Different numbers. If 3 numbers match between the two sets of 3 numbers I want Column G to display "CCC". IF 2 numbers match I want G to display "CCH". If 1 number is a match I want G to display "HHC". If none of the numbers match I want G to display "HHH".
I can manually do these but it will take waaaay toooooooooo much time to do. If you can do this for me it would be a great help indeed because I'm trying to do other states. With this formula I can use Automate to do a large quantity of numbers.
I am trying to get the information that I have plotted, on a scatted graph, off onto the second sheet an splitting the information. I'm not too sure if this is the right place for this thread, but I am hoping that someone can help?
The original data that is on the graph is in 3 columns. One has the company name, one is the current value and one is the predicted value.
I need this info to come off the graph or from the original data in 2 fields. Customer and predicted value, but they need to be prioritized.
1 needs to be the one with the lowest current and highest potential. 2 needs to be the 2nd lowest current and 2nd highest potential......so on. Then I need to get the highest current and highest potential.......so on.
I know that what I require is quite complex and it may have to be via calculations, but I am hoping that excel can do this?
I am looking for VBA code to solve a current problem. I have a list of numerical (row) values (Column A) that I am sorting the column (by VBA code) in descending order. Column B is the Bin location. These rows are then output to another worksheet (Column 1 shows the amount and Column 2 displays the Bin location and the amount to be shipped from each Bin). After each output, the original (A & B) columns are re-sorted.
If Column A = 27 Column B = Bin1 Then [TABLE]27Bin1;[/TABLE]
The Output should be Column 1 = 27 Column 2 = Bin1 27; written as [TABLE]27Bin1 27;[/TABLE]....................
in column A i have fruit words (e.g. apple, banana, orange...)
in column B i have cities (e.g. london, paris, rome...)
i would like a formula in column C that gives "british apples" when "apple" and "london" are on the same row whilst also giving "french bananas" when "paris" and "banana" are on the same row.
I am trying to get two cells to be used to adjust other cells on the same sheet repeated times.
In a inventory sheet I have 5 columns as such , A1 previous balance, B1 Qty received, C1 qty on hand, D1 last cost, E1 current avg cost What I want, is to be able to enter my weekly received items in the B1 Qty received and the new cost in D1 Last cost cells and have them calculate my current average E1 and update my qty on hand C1 total. This in itself is not the biggest challenge. I was asking if there is a way that after the E1 current average cost and C1 qty on hand are updated by that formula, that the next time I enter a new B1 qty received and new cost in D1 Last cost cell they will update again basically without changing the earlier calculations achieved .
Example: Today I have item X with a A1 previous balance of 10 , with a D1 last cost of $1, and E1 current avg cost $1
I want to receive B1 10 more today at $.50 D1 last cost, which ideally would end up showing
A1 previous 10 (or 20 if adding) , C1 Qty on hand 20, D1 Last cost .5, E1 current avg $.75
Next week I want to receive B1 10 more at $.25 D1 last cost , which then would update showing
A1 previous balance 10 (or 30 if adding), Qty on hand 30, Last cost $.25 and E1 current avg $.58
