I have a brand Master in below format in sheet 1
brand codebrand Description10a11b12c13d14e
and Product master in sheet 2
product codeProduct Description000roof paint001floor paint002exterior paint003marine paint004wood finishes
I need in sheet 3 for each brand
Brcode + Product CodeBrand Description and Product description10000a roof paint10001a floor paint10002a exterior paint10003a marine paint10004a wood finishes
how to build a formula that would allow my remaining UOM and it's associated cells to move up to the days remaining UOM once my time goes below, say 31 days.
View 3 Replies View RelatedI need to create a calculator that tells me how long a invoice will take to be paid using my current processes at work, working days only.
I need to imput the date the invoice is received and then for the rest to be worked out automaticly
I need it to do the follwoing ....
I have been trying to create a macro that would create a schedule for me based on date and a product type. In columns K-N I have an IF statement that tells when a shipment would need to arrive in a row that corresponds with a date (column A is 1/1/2015 - 12/31/2015). On the next page I would like to generate a schedule that lists the dates that all of the products are needed in chronological order. I've tried to use custom functions like finding the nth_occurence but it just gets way too messy with so many shipments.
View 6 Replies View RelatedThe code im stuck on is as below:
I am getting "runtime error 438. Object doesn't support this property or method".
[Code] ..........
So I have a formatted empty template listed as BUILD, It has two main parts, a left half that serves as a key and a right part that I want to populate with data.
So the first part of the code copies the key/margin then as I commented I want to start building sets for.
The second part of the code I'm trying to run a loop through user entered data stored in listbox2 grabbing each entry and pasting it in the first row of each set.
Is it possible to build a simple input dialog box with only code?
I'd really like to avoid having a userform permanently in the project.
Ideally it would either display and be dismissed with code only, or be constructed as a real userform, displayed and dismissed, then deleted from the project afterwards.
I am aware of excel's built-in input dialog box, but I'd really like to get away from it's awkward keyboard functionality if it's somehow possible to do. Although that would be ideal if there were some way to make the textbox's behavior to the Home, End, Tab and arrow keys more like that of a normal textbox, but I am unaware of any such options.
I am building a table for a group of vlookups, and I have a bunch of variables that need to be defined. I need to identify each and every possible combination of about seven different terms.
E.g. this simple table:
A B C
1 red1 org1 yel1
2 red2 org2 yel2
leads to this list:
red1org1yel1
red2org2yel2
red1org2yel1
red2org1yel1
red1org2yel2
...
etc.
This model is almost exactly what I am starting with, except there will be six or seven columns, depending. Also, no two variables within a given column will mix with one another, e.g. no "red1red2yel1" etc.
Rather than do this by hand, I thought there must be a programmatic solution (or even a little formula).
I need to build up one dynamic report.
Here is the task:
1) Everyday workers fill up with records in each sheet (record count can be different, because there is a "List" table)
2) Each day is in one separate sheet (for example: 1st June is in sheet with name 1, 2nd June is in sheet with name 2 .)
3) ALL sheets are named 1, 2, 3 ... 31
4) And in the sheet with name "ALL records" must be generated all records from sheet 1,2, ... 31
Here is my Example
I'm trying to build a rota for work, with the aim to gradually make it more and more complex.
So:
First step - I have made a basic skeleton, Names going down, Monday with beginning and finish, Tuesday with beg and fin etc to Friday going across. With hours worked during the day being calculated, minus 30 mins for lunch.
=(24*SUM((D5-C5))-0.5)
Second step - Now, for instance if you begin or finish work between the hours of 11:00 and 14:00 I need to make it so that you don't loose that half an hour.
I am trying to build a list to put into Microsoft access
Cell A1 has "
Cell B1 has 8W###
Cell C1 has " Or
I want a formula that makes D1 "8W### Or ". edit: I got it =(A1) & (B1) & (C1)
I kept trying to use "" and it would work.
how to build and execute formulas and functions. My love for messing with excel started with an interest in personal financial management utilizing amoritzation tables, budget planning, paychecks, tax bases, etc., My wife sees it as strange but I love messing around with the Excel formulas. For now it's all self taught but, as stated, that will be changing.
So far, I haven't been able to find how to execute the following scenario.
For brief background, I'm attempting to identify the results of various outcomes of an individual currency trade by changing the investment size given the results of the last "x" amount of trades. Put another way, I'm running data sets on Forextester wherein I trade a historical period of time on a specific set of currencies, i.e. "Pound/Dollar." I'm wanting to know what the resulting balances would be, or would have been, if I implemented a money management strategy that increased my investment on consecutive trades when there was an "x" number of consecutive "wins" (ending account balance more than beginning) on the previous "x" number of trades. and decreased my investment when there was an "x" number of consecutive losses (ending account balance less than beginning account balance) on "x" number of trades...
So far, I've built the formula so that if I input the beginning and ending balance of a specific trade and Excel populates a "W" or "L" (Win or Loss) in the "Results:" Cell which was pretty simple. The following is one of three scenarios.
Strategy
Conservative:
Start with risk 1% of Account balance (which is $10,000 for example)
The Rule: With each open trade reduce the account balance used by the amount risked on opened trade
(For consecutive trades, assume current trade results in a total loss of investment. Thus, $100.00 of the $10,000 accounts means you're working with $9,900 on next trade)
The Rule: After 3 consecutive winners that make (1.8 * Risk percent used by the trade * account balance used by the trade) or greater in profit, increase risk to 1.5%
First trade Investment: $100.00 plus minimum of 80% gain. Second Trade Investment: $99.00 plus minimum of
80% gain. Third Trade Investment: $98.00 plus minimum of 80% gain. For trade number four, the investment amount would now be 1.5% of the total account balance.
After 1 loss reduce risk to 1%
First Trade Investment $100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%.
After 2 consecutive losses reduce risk to 0.5%
First trade Investment $100.00 and you lose - size of loss doesn't matter. Next trade opens with investment of 1%. Second trade is also a loss. Thus, third trade invests 0.5% of total account balance.
After 4 consecutive losses reduce risk to 0.33%
After 2 consecutive wins that make (1.8 * 0.33%* account balance used by the trade) in profit, reset risk to 1%
(Two wins = 1% investment, regardless of number of previous losses
There are two additional scenarios but they all follow the same pattern. The differences are only in the actual risk percentage. I can modify the formula as needed.
I need to run a loop to look up several columns.
My problem is that I need to build a formula that can sum each value in this row from the columns found when I'm done looping.
And then I was planning to just use "AutoFill", because of the large amount of rows this procedure has to cover.
Can I somehow create a code that will put each column number in memory, and then when done create formula like = "=SUM(G3, I3, AF3, BB3)" or something like "=SUM(& "i1" & 3, & "i2" & 3.... etc etc
I'm a little worried for overdoing loops as they have a tendency to slow down the speed.
I'm trying to build a dictionary of abstract words. It seems that all abstract words are built from less than 30 other indefinable words. I need a macro that can generate column H. I would prefer a macro because ultimately i will probably have a database of about 300 words. What I need is a macro that will go down column G, see the word "after" then find that word in column e and the corresponding number in column f, then write that number in column H. Every word that occurs in a row in column g, should ultimately be converted into a number in column h.
View 6 Replies View RelatedMy problem is that i have to create 100+ graphics (this my boss want),so after i create the first table i just copy past and change the data range and the name of the table. I want to automate this process with macros so i record a macros .
Explaining: "Eono1 is the name om my workbook.
So what i want ,the macros automaticali create new graph ,and change the data range ,(my data range is for january to december i row so i want to grab the next row from C3:N3 to become C4:N4)
Lets say i have 48 countrys (names R3C2),macros should automatically stop after c48:N48 for 2014,q48:ab48-2013 and AE48:AP48.(The last graphic is created).
Here is the macros and the exaple table with chart
Code: [URL] .......
Code:
Sub macro()
Range("B2:N3").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Eeno1!$B$2:$N$3") -----this one unchanged
[Code] ......
I am trying to do is to build an array from a series of cells that may or may not contain text.
For example cells A1:A6 may be equal to red, orange, yellow, green, blue and purple,
but each cell may also be blank.
So I might also have blank, blank, yellow, blank, blue, purple.
Is there a way I can make an array of just {yellow, blue, purple}? and then the next time it may be {red, blue}
Or can I index only cells that have text?
In the end, I just need a way to make a selection from only those cells that have text.
I'm trying to copy a pivot table cell onto another worksheet that has a table with Month-Dates across the top in columns and left-most column has several cells, each with a named range. I get an application error with:
Sheets(shtSrc).rngSrc.Address.Copy _
Sheets(shtDest).Range(Sheets(shtDest).Range(rngDest).Row, Dt.Column)
shtSrc and shtDest are Strings passed in to Sub for worksheet name.
rngSrc is a Range passed in to Sub.
rngDest is the Named Range of the destination cell.
All of the values get passed in; I'm just not using the range properly I think.
I'm not married to what I've got so far. Basically, as long as the pivot data gets copied at the intersection of the correct Date (Column) and Row I'll be happy.
This line will give me the current weeknumber
wkNumber = DatePart("ww", Now(), vbMonday, vbFirstFourDays)
But also i would like to have a dynamic array that will give me the dates of this week
Currently i do that using vlookup in a table stored in my personal.xls but that is no longer possible as i have to distribute my macro and i no longer have that possibility.
I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit (n) (in this case n = a building; there are 100 buildings), a few sheets (B) containing parameters & settings, and then 1 main sheet (C) which does complex calculations pulling & combining data from sheets (A) based on the parameters in sheets (B). The workbook is so large, (100MB+) that it has to be set to calculate manually.
One of the settings in one of the sheets (B) dictates which building (n) is currently being looked at. As such, sheet (C) effectively builds a report for building (n) and so can report on just one building at a time, that building (n) being determined by a drop-down box listing all of the available buildings and that list is in turn defined in one of the sheets (B).
If I want to get a consolidated picture for all buildings I currently manually change the building choice (n) in the dropdown box, and then run a macro (m1) which updates the report to show what I want, selects the specific bits of the report I want and copies the selection, switches to a separate pre-formatted consolidation sheet I have created (D), jumps to the last row in that sheet, pastes values and then jumps back to Sheet (C).
I then need to manually change the selection (n) and then rerun macro (m1).
What I would like is another macro (m2), which automatically loops through the different values for (n) so that the whole process is automated. Then in a perfect world, perhaps even a separate macro (m3) which would give me a list of tickboxes so that I could select specific properties for which to run the report.
Having problems with input boxes. I'm trying to creat 3 input boxes that will prompt the user to input 'Add1' in cell A1 'Add2' in cell B1 and 'Add3' in cell C1. Then return back to the first prompt box ready for further info - but this time, if it finds info in cell A1 it needs to move down to A2....and so on. Just about down the easy bit and have the three input boxes working - but unfortunatley that's it.
View 7 Replies View RelatedI have recorded certain macros. But after executing macro 'Undo' do not work. How can I do it ?
View 3 Replies View RelatedI have been lucky building codes, but this one has me bad. I already have a code that pulls data and puts it on a spreadsheet. To activate this I put in a date that I want to pull data from the PI server. What I want to do is save that data into an excel spreadsheet for history. I want it to know the date and save all the information. Then at a later time I can pull the data back up again. I can currently get it to move the data, but it doesn't sort by date and when you do the macro over it writes over what i just had. How do i get it to sort by date and enter into new cells?
View 2 Replies View RelatedGot something I need to do that I have never done.
I have a column on a worksheet1:
NBC//1
ABC//1
ABC//2
ABC//3
ABC//4
CBS//1
CBS//2
I have a column on another worksheet2:
NBC
ABC
CBS
In the column next to the above on worksheet2, i want to pull the numbers in for that (the numbers are contained in ONE column):
NBC | 1
ABC | 1 2 3 4
CBS | 1 2
Im not sure how to go about this?
In the example I attached, on sheet 2 column B, that is where I want the numbers to build to.
I am trying to build a macro using two files. The file 'master data.xls' has student details in a table containing 10 column heads as attributes (roll, name, address, email etc). The file 'Student Profile.xls' has been formatted to show individual student details in the form of a report. I want to prepare a macro such that each time I enter a new record in the "master data.xls", the data from new entry is printed in the format as given in "Student Profile.xls" as a pdf file which is saved with a file name defined by "(roll number) (First Name) (Last Name)" and the same is emailed to the student's email ID as specified in the "master data.xls" with the mail containing an address to the student name (ex: Dear Tom) and a standard text message in the body.
View 1 Replies View RelatedI have some raw data, and in that data are 2 columns. They are Manager and Role. I am trying to create 2 lists, that correspond to their role. I can create a dynamic named range from the column no problem using this formula:
Formula:
[Code] .....
Now, what I need to be able to do is, add a criteria option to the formula. So build the list off of column B, but only if their role (in column C) is Perm, or Temp.
See the attached spreadsheet : dynamic range criteria.xlsx
A typical Design Matrix is shown in the attached Workbook. There are two domains of Merged Cells that make up the Headings of the Matrix; FRs (Functional Requirements) and DPs (Design Parameters). Given a Hierarchical List of FRs specified by the User, the User would like Excel to bulild the Matrix Hierarchy of FRs automatically (going down the Worksheet). The DP Hierarchy is the same hierarchy, except transposed and reflected across the Worksheet. The attached Workbook has up to seven (7) levels, but the ability to go create up to 10 levels is desired.
View 8 Replies View RelatedI have a workbook with two spreadsheets, one blank and one with a Header row and a list of employee names and random dates that will continue to be added to at the bottom per a copy and paste.
What I need is to have a form dropdown on the blank sheet (per a cell reference) that shows employee names and dates that has that name next to it in the data sheet.
The employee names are in three columns Last, First, and Middle and the dates are in a separate column (A).
I guess I need a VLOOKUP that can take the range and pull into the dropdown the employee name and every date associated with that name.
I write a excel file (with macros included) to tackle the datas regarding stock market.
The excel file encompasses some information about financial reports, and some functions (macro) like sorting and filtering.
I'm wondering if I can build it as an windows 8 App and put it on the App market.
Or, in other words, can I make a windows 8 App by the functions in Excel ?
Can I do that ?
I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.
5. Second sheet "Project" in Range("A:C") contain example of tree like: ...
A single worksheet holds all the values I need to move to various worksheets in the destination workbook. The destination workbook is MASTER.XLS and is already open. The source workbook has various names.
I have the macro walking through each value in column E of the source worksheet. When a match occurs, the corresponding cell in column F has the destination worksheet name, the corresponding cell in column G has the destination cell address and column H has the destination value (string value).
I have dim statements for SheetName, CellAddr and CellVal ; all set for String. I have been playing with "Offset" as well as "Select"ing through the worksheet hierarchy to drive to the desired destination cell. All seem to be more work than necessary and none work properly.
What I am looking for is a set of macro statements that I can use as a "template" within the balance of the macro I have written. I would also happily accept recommendations about books that provide a step-by-step approach to learning the capabilities of Excel's VBA functions. I know from my limited programming background that there are many ways to do the same thing. I'd rather start with the most efficient rather than burn lots of hours experimenting.
I would like to build an index sheet from multiple sheets in a folder(directory).
Let's say I have a folder with sheetA.xls, sheetB.xls and sheetC.xls. I would like to automatically create an "index" sheet that lists the sheet name and info from cells B2 and cell C3 all on a single row. So my index sheet might look something like this:
sheetA Portland Oregon
sheetB Seattle Washington
sheetC Reno Nevada
