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?
I have a worksheet (with if statements) that I throw numbers from a report into and it uses those if statements to calculate the number of items and money earned. The problem is that sometimes employees use different key words for their items so the formulas dont pick them up and this skews their results for the month. Also it's hard to see where someone messed so I'm trying to write a code that reads each row for a given selection and calculates the sum and if that sum is 0 then we can look for where the employee made an error. This is what I have:
Dim RngToSum As Range Set RngToSum = Selection.Rows For Each Row In RngToSum If Application.WorksheetFunction.Sum(RngToSum) = "0" Then MsgBox "Lets play find the error" Selection.Rows.Interior.ColorIndex = 3 'red Else Application.WorksheetFunction.Sum(RngToSum) 0 Then MsgBox "Congrats there's no errors" End If End Sub
The problem with the code is that I want it to read every line and if there's no errors then have it say "congrats no errors" after ALL rows have been checked because right now it pulls a msgbox per row and I don't want to go through hundreds of no error msgboxes. However, if even just one line has an error then have a msgbox come up.
Also there's something weird where this code wont pick up an error for a row if i highlight two rows (one row without an error and one with) but if i highlight just rows of zeros then it'll pick it up.
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.
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".
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.
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.
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.
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.
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.
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.
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 'firstname.lastname@example.org'
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?
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)?
I have a spreadsheet containing Date, Description and Value in a three column setup. I wish to generate one summary for each unique description, based on a table of types. I can then say that for instance "Apple", "Oranges", "Lemon" are all part of a "Fruit" group.
The lookup would then find every row containing words in this group and summarize the values for them all.
I having trouble to sort out and summarize inventroy products for each week. I want to sumarize all weekly inventory in summarize table. Please see attached file. Pls keep the formate as i shown in attached spreasheet.
She has a spreadsheet with all of the employees of the company listed. Each employee is associated with a cost ceter (there are several hundred employees, and about 90 cost centers). Each month we count how many people are in which cost centers. I am using a sumif equation, and that works well, however, we keep the historical data, as well as the budget data in the same spreadsheet, so each month the "sum_range" column needs to change (ie column 4 for feb, 5 for March). I can use a find and replace to address a different column in my equation, but my boss would prefer something simpler.
I am trying to write a series of macros to automate the creation and population of a summary tab for transmission of patient results to our Laboratory computer system and have hit a bit of a brick wall. One of our analysers creates an Excel workbook for each analytical run with each patient sample analysed on the run assigned to a separate worksheet. These worksheets contain the patient ID (lab number) in cell C1 with results for the different analytes in cells F7 to F56. We need to be able to present this data with each patient having a row to themselves (i.e. Patient ID for patient 1 being in cell A2, test result 1 in cell B2, test result 2 in cell C2 etc, then patient 2 taking row 3 etc). Row one is populated with the field Sample name in cell A1 and the cross-referenced test IDs running across the rest of the row. As analytical runs can contain varying numbers of samples we need the Summary tab to be able to cope with a indeterminate number of worksheets.