I have inherited support for the an Excel 'program' that seems to be fairly unstable. I am currently having problems with the following code in a subroutine (executed when the user presses a command button):
With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" .PrintArea = ActiveSheet.UsedRange End With
Sometimes it works and sometimes I get the following error: Run-time error '1004' Unable to set the PrintArea property of the PageSetup class.
… on the .PrintArea = ActiveSheet.UsedRange
I am having a hard time figuring out what is causing its wishy-washiness. Running the following code (launched from worksheet named 'consumer' with a command button):
I have one more question, for now... I am imputing a date into a cell that is too narrow for the date, so the cell outputs xxxxxxx. How can I get around this without changing the cell width.
I discovered the following Macro (on another site). What I'm attempting to do is make data entry simple by entering a date such as 012209 and have a macro convert the entry to 01/22/09 as a legitimate date field. On entering 012209, the macro displays a value of 09/01/2021 with an actual cell value of 1/9/2021. I expected a value of 01/22/09.
Entering 010109 displays a value of 01/01/2009 with an actual cell value of 1/1/2009. I expected a value of 01/01/09 (I can live with this, just didn't expect a 4 digit year).
The result of my formula should be blank, but 1/1/1900 appears instead and I can't get rid of it. Does anyone know why or how to fix. I can't just change the font color as it screws up other formulas.
In this case the Vlookup is referring to an apparently blank cell with no formula in it. I googled 1/0/1900 and saw a few references that said formatting a cell with zero in it as a date would cause this result, but there is no zero in this case.
Cell A CellB Cell C Name Hour Work Hour Rest Alan 3 1 Alan 5 0.5 Steven 2 0 Alan 3 1.5 Ricky 5 0.5 Steven 3 0
If I use sumif using VBA, I would have to Evaluate("sumif(Sheet1!A:A,""" & "Alan" & """, Sheet1!B:B)") Evaluate("sumif(Sheet1!A:A,""" & "Alan" & """, Sheet1!C:C)")
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
I have looked through the forum and found pits and peices but can not put the puzzle together. Found the VBA code in my example from a earlier post but there was no final answer to the post.
Trying to have the user put in a date range via command button. Fro mthis date range the data thats falls within that range is copied to a report sheet. Will also need to have all the old data from a earlier querry removed. Have attached example sheet.
I am trying to pull 4 cells (Q3:T3) from multiple workbooks into a master workbook. When I run the below macro, I only have the first column of the copied data returned, and can't figure out how to have it paste all four cells.
Sub ExtractData() Dim wb As Workbook Dim TheFile As String Dim MyPath As String Static CopyCell
I have written two macros to create a pivot table that displays data by month. I created a column that is a flag that indicates if the if an instance occurred in the last 4 months(later used as a filter). The issue is how to handle defining the last 4 months. If the current date is prior to the 16th, I want to define the last 4 months as not including the current month. If it is after the 15th, I want define the last 4 months as including the current month. Currently I have two different macros and I make the decision on which to run. I would like the macro to handle this for me. Below is the cell formula that I use to set the flag if it is in the first half of the month.
What i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
I am trying to prepare a time and attendance system for our warehouse.
I have a download of clocking in and clocking out times.
I want to look up the times based on the person name and the week commencing date.
I want to create a main summary sheet which will have two combo boxes, one for names and one for weeks so that when these are changed they update the times.
I would like this done in VBA because when the times are updated I want them to appear as values and not formulas.
Also, the data range will be large (approx 13000 lines for 15 weeks data) so the code needs to be efficient enough as to not make the file too big.
I'm trying to create a summary sheet as a second sheet in an XL workbook. I need to pull data from another sheet in the workbook, but only from a date range entered on the second sheet.
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
basically I am trying to total this spreadsheet up to work out a sum for the month by customer number. some customers made a payment in jan 2012 and paid again in jan 2013, some paid twice in one month.
what needs to happen is
it needs to have customer number going down and the month/year going across the top with a summary paid for that month
I have a workbook that I'm using to tracking staffing patterns within a mental health agency. When the workbook opens the user is asked to pick a date range and an office location. I've placed code into the userform that pre-fills the "start date" with today's date and the "end date" 7 days from today's date. I would like the user to be able to enter a unique date range should they wish but I have yet to figure out the coding to accomplish my goal.
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date Dim projEndDate As Date Dim DateStart As Date[code]....
excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.
Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.
Sample Statement below
"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?
I am looking for VBAS code to validate a date when a user enters a date. The date format must be in format dd/mm/yyyy for eg 16/05/2014 , 13/06/2014 etc
If date not in this format msgbox to advise user date format invalid
For instance, in travels.xls a table in the 'data' sheet lists destinations versus people and the data of travel.
What i'd like is to assign a macro that would have a breakdown of the number of travels per destination that month. Where there is no travel that month, that destination is omitted.
I can work out how to use SUMPRODUCT to produce the number of trips per destination within that month, but stuck as to how to get it to display it using a macro.
Should I use a loop to loop through all destinations, copy that information to the separate sheet and then another loop to delete destinations with zero trips?
I have a spreadsheet, whereby I have a list of items that grows regularly, but also have a brief summary of items, however I would like the summary to float, so that it always appears on screen, and I don't have to hunt for it on the spreadsheet.
Sheet: Summary = All rows of Masterdata WHERE Product quantity Is Not Null (<>0) starting from row 5 in the summary sheet. note that if a value in quantity has been reset in the Masterdata, the Summary has to be updated. I do not want to use an advanced filter because the whole workbook needs to act like a portable template for various users which once in a while gets updated using external connection with a ms access database. ( Import of access report). I have no idea whether a copy function or customized pivottable (does not works because too much data), ... would do the trick.
I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))
Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long lngBegin = 9 'beggining of data lngEnd = 232 'end of data lngCmp = InputBox("Please enter the date", "Begining of the week")
I have a very large table that I want to be able to summarize neatly and columns that have zeros for a specific person, so its easier to input data later.
I have 9 sheets of sales data, some with over 50,000 records. I need a way to present this information in another worksheet so I can bring up just the data based on individual brands. I understand a pivot table is needed however I cannot see how to gather data from multiple worksheets.
For example:
I need to look at all the different sales for Product A, which appears in multiple worksheets. Product A is broken down to store-level, with records for number of sales in each store, one record for units and one for value (for the same store and product). Is there a way I can get the data for all of Product A in one worksheet so I can then easily add the totals across all stores by both units and value?
E.g. Store 1 - Product A - 10 units Store 1 - Product A - £20 Store 2 - Product A - 7 units Store 2 - Product A - £14
A) Is this even possible to do in Excel? I've seen similar ideas in foums, Excel help, etc., but they are all limited to worksheets within a single workbook.
B) My front half logic will work if I can do the back half. But if that's not possible, is there another way to accomplish this?