write a vb subroutine that accepts a date from the user and then displays a summary of the data (which i have) for that day in a message box
View 9 RepliesI 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):
Sub subConsumerFacesheet()...................
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.
=IF(ISNA((VLOOKUP(VendorTracking!$P10,LeadTimeVlookup,2,FALSE))),"",VLOOKUP(VendorTracking!$P10,LeadTimeVlookup,2,FALSE))
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.
This is my excel sheet
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)")
my formula is in B1. If in A1, there is letter Y, the value in B1 should accept only 3-digit numbers.
View 2 Replies View RelatedI 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.
View 2 Replies View RelatedI 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
[Code] ....
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.
ActiveCell.FormulaR1C1 = "=IF(RC[-3]>EOMONTH(TODAY(),-5)+1,1,0)"
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..
View 9 Replies View RelatedI 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.
View 11 Replies View RelatedConsider two columns (these columns are part of UNIT TESTing workbook).. result column shows the result of test case executed on respective date
DAte Result
17/1/2008 PASS
18/1/2008 FAIL
18/1/2008 PASS
19/1/2008 FAIL
22/1/2008 PASS
22/1/2008 PASS
How do i calculate using VBA statements :
1. the number of PASSED TEST CASES that are excuted today.
2. Number of all PASSED TEst cases till yesterday.
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.)
i have attached the spreadsheet
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 attached the spreadsheet.
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.
[Code] .....
Attached File : Staffing Report 1.90.xlsm‎
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
My macro asks for a user input in DD-MM-YY format (which is the same format of all cells in the Excel worksheet).
I then instruct the macro to paste the string into cell A1 in 'TEST' sheet.
However when it pastes in the format is MM-DD-YY with the user input DD being the worksheet MM etc.
For example 06-04-14 becomes 04-06-14.
My code is below:
[Code] ......
Attached File : TEST.xlsm‎
I have a page of data that i need to summarise/calculate, i thought sumif would be the correct formulae but i can't get it to work...
Sheet 1 - Data Recomds Emp Name, Weeks 1-52 showing no of hours to adj
Name
WK1
WK2
Wk3
WK4
WK5
WK6
Oliver
-1.5
[Code] .....
Sheet 2 - Summary by month - to Calculate the no of hours for the period per employee
Name
Month 1
Avery
Require Sum for employee Avery Wk 1-4
[Code] ..........
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.
View 2 Replies View Relatedeg.: productlist + order amount + sum
Sheet: Masterdata
Column A: Productname
Column B: Product price
Column C: Product quantity
Column D: Total (=B*C 'obviously)
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")
Lngcmp1 = DateAdd("d", 1, lngCmp)
lngCmp2 = DateAdd("d", 2, lngCmp)
lngCmp3 = DateAdd("d", 3, lngCmp)
lngCmp4 = DateAdd("d", 4, lngCmp)
lngCmp5 = DateAdd("d", 5, lngCmp)
'lngCmp1 = lngCmp + 1
'lngCmp2 = lngCmp + 2
'lngCmp3 = lngCmp + 3
'lngCmp4 = lngCmp + 4
'lngCmp5 = lngCmp + 5
lngResults = 0
lngResults1 = 0
lngResults2 = 0..................................
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.
View 1 Replies View RelatedI 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?