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......................
View 2 Replies
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
