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 RepliesI have some cells which are highlighted in column A.
I need VB Code to copy the whole highlighted row in Sheet2 as shown. Once it is copied then clear the highlighted cells in column A of Sheet1.
VBA code that can highlight all columns except ones highlighted?
View 1 Replies View RelatedI 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.
View 3 Replies View RelatedLooking for some code to clear cells which are not highlighted
i.e. clear everything from row 2 downwards (Columns I:P) but exclude cells which are highlighted (not conditional formatting)
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 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
build a formula that can lookup and summarize.
I attached file >>>Lookup summary.xlsx
Date ModelStationLot_SizeSampleReject
04/7/07APCA771
04/7/07CPCA64320
04/7/07BPCA36320
04/7/07BPCA441
04/7/07CPCA110
04/7/07AB/B880
How can I quick summariz these data by VBA ? as below result.
Date ModelStationLot_SizeSampleReject
04/7/07APCA771
04/7/07AB/B880
04/7/07CPCA65330
04/7/07BPCA40361
This is just my sample data. The real data is more complicate than this. I want someone suggest me about VBA code.
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] .....
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?
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 RelatedI 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.
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
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
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.
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?
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......................
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.
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 '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?
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 RelatedI 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 think I need a modified SUMIF formula but I can't figure it. The attached spreadsheet is a sample of what I need. I'm using Excel 2003.
View 3 Replies View RelatedI 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.
View 9 Replies View RelatedHow Can we summarize the following
I tried to use For propety to summarize the number from 1 to 20 but it didn't work properly.
I am trying to summarize data for my boss,
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.
View 2 Replies View Related