Separate Decimal Parts

Mar 1, 2007

I have to make a report which calculates hours worked by employees over a weekend.The hours are stored in variable ,part of code is as follows:

For j = 4 To 32
startday = Weekday(thisDate, 1)
If startday = 1 Or startday = 7 Then
dummy = 0
tempnd = Cells(i, j)
weekndsum = weekndsum + tempnd Else
dummy1 = 1
tempdys = Cells(i, j)
weekdysum = weekdysum + tempdys End If
thisDate = thisDate + 1
Next j
Windows("Report.xls").Activate......................

ADVERTISEMENT

Print Sorted Parts Of Selection On Separate Pages

Mar 28, 2013

I have a macro that copies my selected area on one workseet, for example B2:M120 on Sheet 1, over to A10 on sheet 2, then prints sheet 2 and then clears the data that was just added leaving sheet 2 as a "clean" template for next use.

Is it possible to add code to the print macro that would look at column G of my selected area and print all rows that had say "cat" in column G on one sheet, all rows with "dog" on another set of sheet 2's, and repeats until all rows of my original selection have been printed?

I thought maybe paste the whole selection then filter, hide unwanted rows, print, repeat but I can't figure out how to repeat and alternate what rows are hidden.

I'm open to any way of doing this, we currently do it by repeating the "select area" but as my real life use replaces "cat" and "dog" with a 9 digit number we are having issues with some rows getting skipped while others get doubled up.

View 1 Replies View Related

Link The Numbers In The Form To Separate Parts Of A Large Printing Macro?

Jan 12, 2007

I have set up a form where the user can enter the number of copies they want for 10 items (I will call them item1, item2 etc for ease)

Is it possible to then link the numbers in the form to separate parts of a large printing Macro?

View 9 Replies View Related

How To Separate Whole Number From Decimal

Jan 25, 2014

I have column A which shows the quantity of a product that I have in stock

A1: 20
A2: 20
A3: 20

I also have column D which shows an increasing income, the amount of the increase varies daily but what I need to achieve is that every time cell D is greater than 50 then cell A4 should be the sum of A3 + the number of '50's that were in D3.

So in this example A4 would increase to 22 (because I can spend 100 on 2 items of stock) and cell E3 would show the balance. In this example its 7.35

D1: 18.23
D2: 42.84
D3: 107.35 E3: 7.35

View 6 Replies View Related

Print Parts In Portrait Parts In Landscape

Dec 21, 2006

I have a document needed to be printed with some pages in the middle in landscape page type, the rest in portrait. If using Word it would be easier, but in Excel I cant find the section break to chage page setup separately. Is there anyway to do it. Currently I'm printing the document separately in portrait and then landscape with some page break added and page number modified. However it's quite troublesome and easy to make mistake.

View 3 Replies View Related

Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet

Dec 10, 2008

I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

View 3 Replies View Related

Search Parts Of Columns Looking For Matches In Parts Of Other Columns In A Row

Mar 1, 2014

I have sheets with names of people in columns....some married...some not. When they are married, here's a sample format...

Jones, Donald T | Baker, Sarah Jane | Jones, Sarah Jane | Smith, Sarah J | Jones, Sarah Jane Smith

In this example, I would like to be able to determine which of the Sarah's belongs to Donald w/o having to visually look at each record ( 100,000's of records). (FYI: the names for Sarah would/could be her Maiden Name and possibly a name or two from a former marriage). What I need to be able to do is match and extract the names of Jones, Donald T and Jones, Sarah Jane and Jones, Sarah Jane Smith and eliminate Smith, Sarah J and Baker, Sarah Jane.

In my example, Donald is in the first column, but can be in any column on a row so the name positions are random across the columns. However, the format for each column is then same...Last Name, First Name Middle Name(or Initial) with a comma always after the last name in each column. The length of the last name also varies.

VBA or Formula that will search the cells in the columns of each row and return the names (complete contents of the cells with matching last names) that have a matching last name for that row.

View 3 Replies View Related

Paste Two Decimal Number In Excel Without Extra Decimal Places Appearing

Aug 13, 2009

I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...

View 2 Replies View Related

Formatting Group Of Cells To Have Different Decimal Rules Based On Decimal Value

Aug 3, 2006

Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.

View 9 Replies View Related

Removing Decimal Point While Maintaining # Of Decimal Places

Jun 10, 2009

I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.

View 4 Replies View Related

Conversions Of Decimal Feet To Decimal Inches - Formula?

Apr 15, 2012

I have to make a excel document in which I have length and width in feet and inch format.

E.g. 10.01 in which 10 is feet and .01 is inch

I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .

Just like the calculator here does.. [URL] ........

Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...

Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????

Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000

View 7 Replies View Related

Convert Or Format Decimal To X Digits Without Decimal Point

Aug 8, 2008

I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:

=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))

However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.

View 2 Replies View Related

Calculate Decimal Portion Of Decimal Number`

Jun 10, 2007

I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.

View 2 Replies View Related

Shade A Cell Red If Two Separate Conditions Apply (in Separate Cells)

Dec 3, 2013

I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.

View 11 Replies View Related

Separate Letters And Numbers In A String Into Separate Columns

Nov 20, 2012

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A

View 5 Replies View Related

Separate Numeric / Text Combination Into Two Separate Columns

Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC

The result would be:

302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC

View 6 Replies View Related

How To Separate Text From Numbers Into Two Separate Cells

Feb 13, 2014

I'm trying to separate text from numbers into two separate cells...

Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.

Input: Output 1: Output 2:

Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234

Ideally, I would like to do this with a formula...

View 6 Replies View Related

Separate Workbook By Value In Column Into Separate Workbooks

Feb 25, 2013

I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.

Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?

I have attached a sample of what the workbooks look like right now.

Sample123.xlsx‎

View 4 Replies View Related

Creating Separate Reports In Separate Spreadsheets

Nov 21, 2006

I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.

I want to do this automatically.

I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).

Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.

Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet

There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...

View 9 Replies View Related

How To Move Parts Of A Row

May 30, 2014

I am trying to move some parts of a row (From G:AE) where text is found in G Column. It moves it back 1 column from G

[Code] .....

Attached File : ozeform.csv‎

View 4 Replies View Related

Cutting File Name In Parts

Mar 28, 2014

I have some files on disk where I need to copy a part of the file name to make a search in our database. The files are PDF. They can have a long name of short. The only thing that does not change is the last part.

The last part ends like this:
1234_vw golf_Es_1234567_20140321.pdf

I'm interested in the part in red to be taken out and put in a cell to the right of the file name.

I used the below code that I found googling. Now I just to cut the part out in red to be able use it in my search in a easier way.

[Code] .....

View 4 Replies View Related

Seperating Parts Of A Phrase Using Vba

Aug 3, 2009

I have a phrase in a listbox formated as such: "Company Name / Company ID # / City, State"

I need to be able to seperate the values out into seperate categories:
Company Name
Company ID #
City
State

Is there any way to do this within the code?

View 5 Replies View Related

Copy Only Used Parts Of A Range

Dec 19, 2009

I'm making a project where there's a list of skills and next to each skill is a cell where the user can put how many skill points are in that particular skill. Now, on a separate worksheet is the place where it shows the consolidation of what skills they know, and how many points are in each. My question is, how can I copy over these skills and how many ranks are in each, without copying over the skills in which they put no points.

View 11 Replies View Related

Combining Parts Of Two Cells

Jul 27, 2009

I have a list of dates in column A as follows:

11/1/2015
5/1/2012
8/8/2013
10/22/2015
4/1/2014
4/15/2014

In column B I have set of data as follows:
C 2010@101.0
C 2011
C 2011@101.0
C 2012@101.0
C 2013
C 2013
C 2014@101.0

I need to combine the day and month only in column A with the year (in positions 3-6) in column B

As an example I have "11/1/2015" in column A and "C 2010@101.0" in column B. The desired result in column C is "11/1/2010".

I have tried using the left function in column A with the mid function in column B but haven't had any luck so far.

View 2 Replies View Related

Cutting Columns In Two Parts

Nov 28, 2013

I have some values in column A which looks like those in table which is shown down bellow. I have somewhere in the middle of that column one empty cell. Of course i have empty cells after the last value. I want formulas or macro or anything which will cut that column in that empty cell and all cells bellow the empty one put in column B from A1, and all cells above the empty one put in column C from A1. I tried everything but i have problem because i dont know how to eliminate empty cells bellow the last cell with value.

ABC
117
228
339
44
55
7
8
9

View 3 Replies View Related

Create A Userform That Has Several Parts To It

Aug 15, 2007

I want to create a userform that has several parts to it.

1stly i want to copy information into the text boxes from a macro.

2ndly the number of text boxes could be infinate (in theory), i want 1 for each set of info

3rdly i want a little option tab box thingy for each box,

4thly the boxes and userform to adjust to fit the optimal size

View 9 Replies View Related

Protecting Parts Of A Sheet.

Dec 20, 2008

I am going to use Excel sheets as computer exam forms. What I need to know is: Is there a way of protecting parts of an excel worksheet from alteration? I want a sheet that will accept answers in specific areas only, and will not accept entries or alterations in other areas.

View 9 Replies View Related

How To Select Parts Of Text

Mar 1, 2010

Does anyone know of a way to select fragments of the following text?

The string is "toby.cameron1".

I need to be able to select just the "toby" and "cameron" parts.
Also this string is variable in that it might be "james.brown" or similar without the number at the end.

I can't use a Left or Right statement because of the varying length of names and the uncertainty of having a number at the end.

Is there a way I can copy the start of the string until the period. And do the same from the end but ignore the number whether it's present or not.

From that, is there anyway to copy the numbers off the end of a string?

i.e. my string is ASY-623 or DRGN-12 or WDSR-7

The string will always be letters a dash and then numbers.

How do I copy only the numbers?

View 9 Replies View Related

Save Parts Of Workbook

Mar 27, 2007

way to save just a work sheet insteed of the whole workbook.

it is because i have 9 sheets a main menu and 8 differant departments the idea of it is is for a stock take and i want to save them daily so i can goback over them at a later date.

View 6 Replies View Related

Finding Component Parts Of A Sum

Apr 2, 2007

I often come across a situation where I know a given total, and I know it comes from numbers within a given list, but I do not know which particular numbers they are. The onyl way to find out is to add every single combination of all the numbers in the list. I want to know if there is any formula within Excel which would help me to do that, given that the numbers of combinations are:

Number of items in list Number of combinations
1 1
2 3
3 7
4 15
5 31
6 63
7 127
8 255
9 511
10 1023
Obviously, I would like to be able to do this for any number of items.

View 4 Replies View Related

Copying Parts Of A Cell

Apr 13, 2007

I have a cell located in $X$1 that is always populated with a person's full name.
I need to display their email address, which is the first letter of their first name then their last name + '@company.com', in another cell on Sheet2

So for example 'Bob Smith' would be 'bsmith@company.com'

How can I go into cell X1 of Sheet1 and copy the first letter & the remaining letters after the space in between the names and paste them into Sheet2?

View 9 Replies View Related

Allow Users To Intall Parts Of Add-in

Nov 28, 2007

I've written a VBA add-in containing a couple of custom functions and a couple of right-click menu items. I read somewhere that the best approach is to keep them all in one add-in as this will reduce the load time. Need the best way (if there is a way) of adding a checkbox menu so that people can choose which functions/menu items to install (so that it's a sub-menu system of the main add-in menu)?

View 2 Replies View Related

Take A Number With Several Decimal Places And Round It Up To Two Decimal Places

Feb 27, 2014

I need a formula to take a number with several decimal places and round it up to two decimal places to either .33, .66 or, .00 if its above .66.

For example, 4.23423423423423 will be 4.33
4.43453453533434 will be 4.66
4.8353453453 will be 5.00

Lets say the number is in cell A1. What formula would do this?

View 3 Replies View Related

Dividing Column In Equal Parts?

Sep 15, 2014

Dividing column in equal parts divide a column in groups with the same rows and the same total sum? For example, I have the column A as following:

8,942

2,807

2,568

5,818

5,818

596

8,942

4,390

1,607

890

8,772

1,103

The total number of rows is 12 and total amount is 52,254

I want to divide the column in three equal parts with the same total. So each group must contain 4 rows with the total sum 17,418

View 9 Replies View Related

Dividing A List Into Equal Parts?

May 23, 2006

Have a list of information and would like to divide that list by a number that the user enters. Then from the total count of nonblank rows divide by the numeber entered by the user didive the list into equal parts and print out each group with a page break per groups.

Have attached an example.

View 6 Replies View Related

Extracting Parts Of Data In A Cell

Sep 1, 2007

i have a cell which contains data like this

in cell A1
12. John Smith (67)

There are hidden spaces in cell a1 as well which are not needed, which i can get rid of using TRIM so that may need taken into consideration ??

In Cell A2 i would just like John Smith
In Cell A3 i would just like the 67

the number and . at beginning is not needed just the name and number (no brackets)

View 9 Replies View Related

Reset Parts Of Worksheet Automatically, But Not Others

Dec 1, 2008

First, ignore the colored portions. This is how I kept track of what I had completed in the form as I created it. Clients attend class once a week and would be marked present on the corresponding day of class. This increases column #3 "classes attended" to "1". What I would like is for my individual classes sheet to reset every Monday (as the date of the report on top) but for the "5groups" worksheet to keep count of the classes attended. Does that make sense? Please, any imput would REALLY be appreciated.

View 14 Replies View Related

Macro To Look At Certain Parts Of A Cell And Compare?

Jun 9, 2009

I have a list of Expense Codes in Column A. They look like this
0010-0020-8200-70.

Where
0010: Represents a Location
0020: Represnts the Business Type
8200-70: Represents Expense Type

I also have 3 tables on the spread sheet, which explains what these codes mean.

I need a macro that will look at the numbers in colum A and return the three types of expenses so they dont have to be looked up manually.

View 7 Replies View Related

Summarize The Red Highlighted Parts Of The Code

Jun 26, 2009

I have the following code used to fill a listbox control .. and I added some conditions to the code in order to give special authorities to specific users depending on their position and unit? Authority, UserPositon and UserUnit are functions give the user's position, unit and his authority. Can we summarize the red highlighted parts of the code because I am going to use these parts in many other forms of my project?

View 3 Replies View Related

How Do I Set Up A Formula For Parts (or Units) Per Hour

Apr 12, 2006

I'm trying to set up a spreadsheet that tracks total hours worked and total
units produced. Then I need to have a column that shows how many units per
hour were produced.

Currently, I have something like this:
Column A is in elapsed time [h]:mm
Column B is a Number with two decimal places
Column C divides Column B by Column A

However, I get strange results. For example:
Column A is 6:24:00
Column B is 13
Column C shows 120.00

13 parts in 6:24 hours should be something like 2.1666 parts per hour!

View 10 Replies View Related

Separating The Parts Of A Mixed Number

Nov 4, 2007

Is it possible to separate the parts of a mixed number? I have a cell in which is generated a decimal number. I can convert this to a mixed number. What I need to do from there is extract the whole number and the numerator.

View 11 Replies View Related

ADVERTISEMENT