I'm trying to run a macro that only shows completed projects, which means all elements of a project have been labeled "Completed". See example below; 1868 is the only project I want to see when I run the macro, 1869 and 1870 are fully complete, so I don't want to see them.
Project #Status1868Complete1868Complete1868Complete1869In Process1869Complete1870In Process
I'm no expert with my vba, record a few macros and make alterations to existing code but this is driving me crazy.
I have a simple script which I would like to update all DB links and all Pivot Table information, which my code does but i'm currently having to run it twice before it shows the correct results.
I have a macro which works well. All it does is save a file to a specified location on my network. However, after a file is saved there is no prompt telling the user that the file was saved successfully leaving them wondering whether the macro actually worked.
VB:
Public Sub SaveAs() ThisFile = Range("X2").Value
ActiveWorkbook.SaveAs Filename:="legdcAudits" & ThisFile & ".xlsm", FileFormat:=52
End Sub
I use a scanner that initiates a f2 command and trips my macro. I would like to use multiple scanners using a 8 port hub but I am having trouble with a scanner starting my macro and another scanner dumping data into my macro before the first one is finished. This is dropping scanned data into my macros. Is there a way to prevent the scan from accepting data until the macro has completed its cycle.
View 2 Replies View RelatedI was given a task of calculating bonus for number of projects per employee. The maximum number of projects per employee is 30 and they have completed different number of projects. Data is as follows:
Column A - Name
Column B - Date
Column C - Project
Employee name repeats one row per project and project repeats as they are working with it.
I need to list individual employee names in column D and the number of projects each employee has done in column E. A project can be saved many times thus creating many rows for that same employee. Do you think it is possible to accomplish this. One formula for column D and one formula for column E. If needed I can attach an example file or take a screen shot of it.
I am trying to replicate the class example on Chip Pearson's site:
[url]
In particular I am interested in having a class that can be accessed by multiple workbooks.
I have a spreadsheet containing milestones for several different projects. The number of milestones per project varies.
I need to get the max date for each project using VBA.
Simple Example: 'Project Name' is in column A and the 'Scheduled Finish Date' is in column B.
Project X has 5 milestones with the following Scheduled Finish Dates: (8/15, 8/16, 8/17, 8/18, 8/19) Project Y has 3 milestones with the following Scheduled Finish Dates: (8/20, 8/21, 8/22) Project Z has 6 milestones with the following Scheduled Finish Dates: (8/23, 8/24, 8/25, 8/26, 8/27, 8/28)
I need the macro to return 8/19 for Project X, 8/22 for Project Y and 8/28 for Project Z.
I want to set up a spreadsheet where i can log people's time in half day intervals. The spreadsheet will be split into each person and AM and PM sessions with different projects listed against different days. The spreadsheet will also list any overtime they have spent and totalled up at the end of a period. I would like to be able to ask Excel to look through everyones projects and total up how many days everyone has spent on each project eg. if there are 3 people working on 1 project what is the total amount of time spent by everyone. I will then later add their hourly rates and overtime rates and want to total all of this up against the different projects.
View 2 Replies View RelatedI am looking for a formula with the following conditions:The fiscal year of the business runs from December 30, 2007 to January 03, 2009
There are 13 periods in the fiscal year ending with different date ranges. For example, period 01 runs between December 30, 2007 to January 26, 2008
There may be 3 capital projects completed in Period 01, 5 in period 02, 6 in period 03 and so on
Task:
count the total number of projects from period 01 to Period 13 with the period end date restrictions for the fiscal year. The formula should be able to count the no of projects for each period individually.
I have a combobox on a sheet which contains a list of projects. I can then pick one, and it will populate parts of the sheet with any sub-tasks that are linked to that project.
What i'd like to do is to cycle through every item in the combobox, and print the resulting sheet....
SO:
select 1st item in combobox
Print page
select 2nd item in combobox
print page
select 3rd item in combobox
print page
etc etc until the last item in the listindex....
When clicking on the sorting button of filtered data near the top of a field, I can select different things to show or not show. In a date field, it starts to arrange things by year, which can be expanded to months, which can further expand to show individual days. All well and good.
But many of my dates do not show up in the year categories. They are instead shown below the years as individual dates. Furthermore, sorting the date field in question is imperfect for these dates. Here is an example where some dates from april are jammed inbetween others:
4/2/20124/2/20124/2/20124/2/20124/20/20124/20/20124/20/20124/20/20124/20/2012
...
4/27/20124/27/20124/27/20124/27/20124/3/20124/3/20124/3/2012
These jammed dates are some of the same ones that are orphaned outside the year groupings. All the dates have the same date cell type. No other filters are applied.
I'm working on a project and am particularly new to the whole excel world (especially with macros). Currently, my boss has some data that needs to be refined and organized. Until I get a sense how to have a macro do everything all at once, I plan on have macros do each step more quickly.
Right now, the macros my boss has given me allows the data to be organized into clusters. Each cluster is separated by client IDs in column C. Each cluster is separated by a blank row. We are looking for clients who have not paid their dues and are marking their unpaid dues with colored rows. The dues that are paid are left as white. However, there are clients who do pay their dues, leaving their entire clusters white. I was wondering if there is a macro to remove those fully white clusters.
I am trying to sort a csv file through a VBScript. My problem is that Excel continues to run as a process on script completion. Running multiple variation of this script results in multiple instances of Excel being present in the WinXP Process Manager.
Set xlObj = CreateObject("Excel.Application")
xlObj.Visible = false
xlObj.Workbooks.Open "C:Test.csv"
xlObj.ActiveWorkbook.ActiveSheet.Range("A1").Sort xlObj.ActiveWorkbook.ActiveSheet.Range("A1"),,,,,,,0
xlObj.ActiveWorkBook.Save
xlObj.ActiveWorkBook.Close true
xlObj.quit
set xlObj = Nothing
As a second part to my question, is there any way to modify the code to allow multiple field sorts, i.e., A1 is Primary, C1 is secondary?
I have a workbook which when opened connects to an external datasource (excel file) and downloads a table of information. While this is happening I have a userform which shows which has a lable saying (connecting to datasource, please wait....). However, when I open the workbook, the userform shows but the labels do not show, the userform just appears white.
View 4 Replies View RelatedI have come up with 5 countif formulas that work perfectly separately but need them to be combines into one big criteria.
=countif(PM[ProjectAge],">=200"
=countif(PM[Customer Name],"*" & purch & "*"
AND so on for each criteria. H
I have an issue on manipulating the data in my worksheet below. I need to update column 'remark' when the customer has pay fully. Example customer 1003 and 1004 both pay completely so I need to update done in column remark. For customer 1002, because not fully pay, so cannot update done.
customer inv no invoice payment remark
1002 A12 100 100
1002 B45 120 0
1003 W12 90 90 done
1004 F12 30 30 done
1004 F17 45 45 done
May I know what function or coding can achieve this?
Recently I come across a file with only 1 sheet, in which is a table only occupied an array of A1:Q49. In Normal View I can see the row / column header of A-Q and 1-49 as usual, but nothing outside that range (only grey colour). Also, the empty cells outside A1:Q49 are all grey too. How can I do that?
View 2 Replies View RelatedI have two lists and wish to compare them to identify duplicate values. I have used Duplicate Values in Conditional formatting but cannot find a way of ensuring an exact match. For example one list has the value 4150 and the other list has other values like 5641509 and 341508, both of which contain the string 4150 but are clearly not the same value. However, the conditional formatting is picking these up as duplicate values.
View 6 Replies View RelatedI'm trying to calculate the number of rows in a matrix that have a complete row of data. I would noprmally do this using nested if formuals and a count function, but the columns of the matrix are filterable so the count won't work.
Is there a combination of functions that will adapt as I filter data?
I have a large workbook with hundreds of rows of data.
I have sorted the data and ended up with product codes in column A and my data between columns C and V.
I want to do a sumif with a difference if that is possible.
I want to sum all of the data in column C based on the product code in Column A. But I don't want to include any rows of data where Column V is blank.
i.e. If my sumif for a product code covered 3 rows C3:V5
I want to sumif Column C dependent on the product code in column A but I only want to include rows where there is a value in Column V.
So if all of the cells between C3:V3 contain data include count C3.
Moving down to row 4 if this row only has data between C4:M4 don't include C4.
Moving down to row 5 if this row has data C5:V5 include C5 in the sumif.
i.e if there was 100 in C3, 200 in C4 and 300 in C5 the sumif would be 400 because although C4 has a value V4 does not.
I want to roll this out across all of the columns C:V using the same method if possible.
We have a spreadsheet in use at the moment which has various lines of information, each line has a customer name, id, request and various other bits of information which needs looking into.
The infomation is arranged onto weekly sheets and is managed manually. Once the customer request is complete, we grey out the background colour. Some outstanding issues background colours are either left blank, yellow or red, depending on whos doing them.
I was wondering if there is a search criteria that we could use that would search through all the sheets in the workbook and find any customer queries that are not greyed out and display them on the results page.
I have the following setup on a worksheet :
5427 Data
5427 Data
5427 Data
6565 Data
6565 Data
7765 Data
all I want to do is have the above table look like this :
5427 Data
6565 Data
7765 Data
The concept being that I can double click that row to see the additional information(i.e. expand to show extra information)
Real life use would be of such, the first column contains the invoice number, the 2nd 3rd etc contain item associated with such invoice.In grouping the column invoice number, you only have to look at one invoice, instead of it duplicating for each item contained within the invoice.
This will need to be done in an efficient manner(over 4000 rows need grouped)
I realize I could just create a list that has one copy of the duplicates, but I want to extract the original information as well. For example, it would look like the following :
Original :
1212 Data
1212 Data 2
1212 Data 3
2323 Data 1
2323 Data 2
Edited :
1212
2323
double click 1212 for :
1212
+Data 1
+Data 2
+Data 3
2323
Note : This grouping will also have to span across the whole row, sample rows :
Invoice# Price Color Weight Item
1212 Column1Data Column2Data Column3Data Column4Data etc.
1212 Column1Data Column2Data Column3Data Item2Column4Data etc.
I am trying to come up with a composite score that if a child takes a particular course and has a grade then make a composite score. Here is an example below.
ABCDEFGHIJK1Math CourseScience CourseReading CourseWriting Course
MathScienceReadingWriting Composite Score Looking for2APGEAPAP A+BC-B- 11003
In the example below I am looking for a composite score of 1100. Here is how I derived at it!
=4*100 + 3.2 * 50 + 2.4 * 100 + 3.0 * 100 = 1100
Code:
Function composite(r As Range, course As Range) As String
Dim d As Double
Dim score As Double
[Code]....
What I am trying to do is record the time when a task is completed on the spreadsheet. For explanation sake, lets say I have list of 10 tasks in column C 1-10. In column B 1-10 I have a button for each task. When you press the button for the task, the time it was pressed is recorded in D 1-10. To put the time in I used =now(). The problem that I am having is, when you press button 2-10, it changes the time in the cell above to the same time. So instead of have 10 different times in 1-10, I have 10 identical times,being the last button I pressed. I tried timestamp, time value, etc
View 1 Replies View RelatedI need to be able to have users complete a range of mandatory cells before they are able to close a workbook.
Foe example cells c5,c10,i11, i18,i22 i24,i26 etc etc need to have data in them before uses is allowed to save or close the workbook. I would like to have a message box prompt to alert them to the issue.
I have a little problem and could use some help please.I created a worksheet for logging orders but now find it too large to cut and paste but have little knowledge of macros
I would like to have any completed rows moved to another sheet in the same workbook ...
This seems easy in theory but it's above my head. I have written code to add text and numbers from a userform to certain cells on a worksheet. Now, is it possible to display a message box after the code executes to acknowledge the code has executed successfully? I have tried simply adding the Msgbox function at the end of the code but that does not work.
View 2 Replies View RelatedI am maintaining a spreadsheet to enter all my sales orders. I need a code which automatically moves the entire row to a new sheet "Completed" when the order status is selected as "Completed".
View 6 Replies View RelatedI've got a password protected spreadsheet with only certain cells unlocked to allow users to complete them. I need the following to work on Excel versions 2003, 2007 & 2010.
Cell E3 has a drop down list with types of car to choose from. One of the car types is "Pool_Car".
Cell D12 should only be used if "Pool_Car" is selected from the drop down list. If any other car type is selected, and the user tries to enter a figure in D12, I need an error message to appear.
E3 & D12 are unlocked cells.
I'm happy for D12 to become locked & only allow it to be unlocked if "pool_car" is selected, but I've looked elsewhere, but can't find VBA or data validation to work.
I am trying to figure an easy way to create a formula or possibly even a simple macro to determine % completed of a particular activity (milestone) within a process. Within each activity there are 5-10 items that need to be accomplished. Each Activity could have a different number of items. In order to provide status at an activity level, each item within the activity will be designated with an N/A (0%), Not Started (0%), Just Begun (25%), Half Complete (50%), Wrap up (75%), or Completed (100%) from a drop down box that the user will select. Each one of these criteria can have a weight assigned to it that is in parenthesis. How do I come up with a formula based on the choices above to reach an overall percentage for the activity at any given time and then tell me status level in descriptive terms of the overall activity....
View 9 Replies View Related