# Setting Major Unit With VBA

Oct 10, 2013 have the following code

With Charts("Chart1").Axes(xlValue)

.MajorUnit = 1

End With

The default is in months. What is the VBA code to change it to years? to days?

have the following code

With Charts("Chart1").Axes(xlValue)

.MajorUnit = 1

End With

The default is in months. What is the VBA code to change it to years? to days?

ADVERTISEMENT

I have two columns. One with the major weight(pounds and one with minor with ounces. The first column would have a 1 the second 8 for 1 lb 8 ounces which I need to combine to say 1.5 Every way I do it it is coming up with 10.5.

View 5 Replies View RelatedI have a large workbook, split into sheets, with very complex formulas in it.

I want one sheet at the end of this workbook to reflect all the data from the

other sheets, if you know what I mean - so it is ALL the data from the

different sheets, in one "all info" sheet.

I also want it to update automatically so I don't have to re-enter lots of data when updating the

separate sheets. Is there any way of doing this? I have looked at some of the

answers on here but I got very lost.

seeattached data set.

Contract Item

Gross Profit

GP %

[Code]....

It is Gross Profit in Dollars and % for a particular project. Each row is a contract Item. The Overall Gross Profit for the month in dollars is 34,114.86 In preparing a short commentary to discuss this 34,114.86 I need to discuss atleast some contract items. If there were no negative results I have often in the past simply written. The top 5contributors to this Gross Profit were contract items 1 -6, out of 42, delivering 80% of the result. I might also go into what actually those contract items were and why they presented such a decent Gross Margin, ie. Costs came in well under budget(estimate). But with negative results, the bottom 4 contract items represent a large dollar loss of-34k which is 100% basically of the Gross Profit itself. If these had even just broken even we would have a GP double the current amount. So they obviously deserve as much comment.

Q1

In excel orsimilarly in say Crystal Reports what is a good way of further sorting these 42records either in a table or chart, whereby say the top 80% contract items that had a major impact on the gross profit are shown and the rest hidden. Ie. All thelittle sub 1000 or sub -1000 amounts.

Q2

How wouldother people here write a commentary on this data set? Can you script something to illustrate?

A1= 8:40, B1= 3:05 (15:05)

C1= I need a formula to give me the number of units between A1 and B2 (1 unit equals 15 minutes)

D1 = the remainding minutes

Example Answer to above would be C1= 25, D1 = 10

All data times will be in increments of 5 minutes and usage of military time is acceptable.

I'm working on a charting application that uses Office charts. I'm wondering what are some of the main reasons people change the base unit from the default, and how common that is.

Some reasons I've identified on my own:You're comparing different monthly metrics, but they could be recorded on different days of the month.Get a zoomed-out view.Compare the max value of each month.

I want to use two separate Unit of measurement according to two condition in a same cell.

View 3 Replies View RelatedI've been looking on the internet for a spreadsheet that lists the various units of measure with conversion factors to other units. I'm not looking for the formulas, just a chart.

View 3 Replies View RelatedI have a list of part numbers that have duplicates because each time there is a different unit price the part number is listed.In my excel file, I also have quantities for each unit price the various different part numbers were sold at. I need a way to

identify: for each part number, what the unit price was for the highest qty sold. I am involved in a project at work and this information is needed.

Here's the table for one game

Date-----Buy(Unit)-----Sell(Unit)-----Net Unit-----Price($)-----Net

Profit/Loss($)

xx/xx----2----------------##--------------2--------------125---------- ##

xx/xx----##---------------1---------------1--------------150---------- ##

xx/xx----##---------------1---------------0--------------165---------- +65

xx/xx----##---------------4--------------(4)-------------170--------- ##

xx/xx----2----------------##-------------(2)-------------165---------- ##

xx/xx----##---------------1--------------(3)-------------180---------- ##

xx/xx----3----------------##--------------0--------------180---------- (10)

Number in bracket means negative.

## means empty cell.

How can I type a formula, so that every time there's no stock left (ie zero net

unit), it will calculate the net profit once.

If you take a look at the spreadsheet you'll see that there are 11 sessions. I'd like to figure out a way by looking at the attendance log to get the percentage/number of people from a certain unit that attended each session. The Y's means that they were present and the N's means that they weren't.

I tried = countif( range,"12WT") which I guess is wrong because it only counts the number of times the word 12WT appears. It doesn't count the number of time a person from 12WT attended the session which is what I'm looking for. How do I go about getting the percentage of people that attended from each unit?

For all the units that didn't attend, is there a formula that would list the units that didn't attend according to the session number? For instance for session one, someone for EC, 9WT, didn't attend. Is there a way that the end result for the formula could be: EC,9WT. Or, does the result for every formula always have to be numeric?

i am currently working on cost analysis of large projects.

I have a worksheet with about 100 headings and i would like to sum up the totals of 'quantity x unit price' for each product.

I tried the sumproduct function but it did not work and i had to do the '=sum(b4*c4,d4*e4,f4*g4,..........) to get the result in the total column. I attach a small sample of the file.

I need to calculate a royalty rate due which is based upon a Unit Price * Unit Sales. The royalty rate due changes at certain levels of sales.

I've attached sheet to hopefully make clear.

I'm trying to return only the following portion of the text from a large list.

So, I want to have just the following phrase appear in a column:

Original

AAM UNIT AAM HIGH 50 DIVIDEND 13 $77,666,423 $1,132,491

To

AAM HIGH 50 DIVIDEND

Original

AAM UNIT AAM HIGH 50 DIVIDEND 13 $77,666,423 $1,132,491

To

AAM HIGH 50 DIVIDEND

Original

FIRST TRUST UNIT FT HIGH DIV EQUITY 15 $13,140,862 $301,295

To

FT HIGH DIV EQUITY

So, I essentially, want any words that appear between "Unit" and the first number that appears before the $ sign (in this case 13 and 15 respectively, but the numbers can be anything - "Unit" stays the same down the list).

I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.

I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.

I can't seem to get my head around this one. I have a list of ranges of prices for different materials depending on the quantity bought. I want a formula that returns the correct price depending on the quantity required.

Please see attached workbook. My desired results are in column S. I can't stop thinking that a nested IF statement is the solution so I was hoping for a fresh pair of eyes on it.

For this ordering tool, I would like to calculate and present the grand total, starting from a fixed range of two columns (each 10 rows), where one has quantities, and the other has the price/unit. Since I'm not allowed to change the layout of the ordering sheet (we still fax orders...), I can not add a column for the subtotals. Also, not all rows are filled necessarily.

I had some success with this as a worksheet function: =IF(ISERROR(H30*I30);0;H30*I30)+..., but I would really like to have it as a part of a big VBA function, since colleages might alter/delete the worksheet formula.

I looked and messed around with For loops, but I can't make it work.

I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.

One method has been using this sumproduct formula:

I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.

I have a series of packages that are denoted by text phrases. Examples:

4/6/12

2/12/12

6/4/12

18/12

24/12

For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).

I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.

I'm using Excel 2010 on a Windows 7 machine.

I have one sheet with a large list of dates and total prices.

I have a second sheet with a list of dates and unit prices.

I want to divide the total price in my first list by the unit price on my second list.

I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.

My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.

This may not be the best way to do this, but I don't know Macros or Pivot Tables.

I am looking for a way with formulas to do the following:

Within a workbook the 1st sheet is the data entry.

In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:

Example:

Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.

G2:G2999 in the same sheet is where the data is.

Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?

I have a list of part numbers which repeat when there are multple prices they were sold at. For each unique part number I need one median price. The list if of about 500 parts but with the various different prices the file is 3700 rows. How can i do this quickly? For each unique part number I need one median price.

View 3 Replies View RelatedWhat formula could I use to return the following results.

1, 2, 3,4, 5, 6 = Manhattan

7 through 12 = Bronx

31 = Staten Island

What I want to be able to do is set up a thing that when you're in the excel sheet you click on it. Up comes a box where you can enter data and when you click submit. It puts that data back into excel spreadsheet. I have attached a spreadsheet to show what I mean. I have deleted some stuff that are unnecessary so don't worry about them.

What I need is for you to click a button that will create a new row/copy into the next row, from A9-AH9. Then the button will automatically bring up a box that will ask for Time, Timber, Clay, Iron, Warehouse, Hiding Place and then will enter that data into there respective cells which would be D20, F20, I20, L20, O20, Q20 respectively. That is the main idea. maybe it would be better to have te button come up with a box asking for the information and then will create the row and insert the data. If it's easier you can just do the box with just time or something so it's easier. I can then do the rest.

Tyvm. This to me seems really complicated so not sure how you would do this at all. i've looked at other stuff on the internet but haven't gotten anywhere.

P.S. If you have a more generic thing similar to this then show me the code for that or upload it and I can change it too suite my needs.

This macro was working just fine, now it generates an error on the line in red.

View 2 Replies View RelatedIt looks like this (log(X/Y))/log2. The X and Y are celldirections (E2 or F4 and so on). How do i setup that formula in excell?

View 2 Replies View RelatedI have the word "Gross" in a cell at the end of each month on one sheet. I need to calculate totals for each month above this columns so lets show it as this:

A B

1 100

2 150

3 220

4 300

Gross 770

How can I set up a macro to always calculate what my gross is setting a condition to find "Gross" and then somehow select the cells above it up to A1? I will have another cell as "Gross" for February under the rows of information for January. I am not at all sure if this can be done.

I am trying to set up a database to collect information on students behavior. We need to collect the frequency of 3 things-time of day, behavior, and correction. Can I do this in Excel? How? Is there a way to write a correlation that will count the occurrence of words?

View 9 Replies View RelatedHow do you set the range with whats below? Right now its looking at 4,2 only and if extend to look all the way to 19,2 it will just copy the whole screen. I need it to look at 4,2 and then move to 5,2 and then so on once it has copied all that information to the sheet ...

View 7 Replies View RelatedI just received a massive worksheet full of information for mailing labels. It's all in columns-which is excel. Is there an easy way to set this up for mailing labels? I've tried to import it into word, but I'm not getting anywhere. Is it possible to change the way excel displays the cells? To a point where I can have the address beneath, then the state, zip, etc?

View 9 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved