Subroutine :: That Accepts A Date From The User And Then Displays A Summary Of The Data

May 11, 2006

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 Replies


ADVERTISEMENT

Code Breaks Sometimes/sometimes Not: Subroutine Executed When The User Presses A Command Button

Mar 10, 2007

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):


Sub subConsumerFacesheet()...................

View 3 Replies View Related

Date Displays As ####

Jul 29, 2006

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.

View 10 Replies View Related

Displays Date In Actual Cell Value

Apr 3, 2009

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).

View 6 Replies View Related

Date 1/0/1900 Displays Instead Of Blank

Feb 8, 2009

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.

View 9 Replies View Related

Can Sumif Accepts More Than 2 Sum_range

Jul 12, 2008

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)")

View 9 Replies View Related

Ensure Cell Only Accepts X Characters

Dec 14, 2007

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 Related

Check A User Input To See If The User Has Entered The Date Correctly

Feb 17, 2010

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.

View 2 Replies View Related

Copy Data Based On Date Range Put In By User For Report

May 16, 2006

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.

View 9 Replies View Related

VBA Paste Only Displays One Cell Of Copied Data

Nov 15, 2012

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] ....

View 3 Replies View Related

Create A Pivot Table That Displays Data By Month?

Jan 15, 2014

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)"

View 2 Replies View Related

Copy Data Based On Date Entered By User For Report Across Multiple Sheets

Jun 20, 2006

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 Related

Summary Of Names By Date

Oct 17, 2007

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.

View 5 Replies View Related

Summary Sheet Using Date Range

Sep 18, 2007

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 Related

Summary Of Pass/Fail By Date

Jan 23, 2008

Consider 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.

View 3 Replies View Related

Prevent The User From Saving The Data Input From The User Form If Any Of Those Three Fields Is Left Blank

Feb 9, 2010

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.)

View 2 Replies View Related

Summary Of Cost For Date Ranges By Month / Year

Aug 22, 2013

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.

View 13 Replies View Related

Prefill Text Box In Userform With Today Date But Allow User To Enter Unique Date

May 29, 2014

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‎

View 7 Replies View Related

Fill Date Across Columns Based On User Date Range Input?

Mar 5, 2014

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]....

View 1 Replies View Related

Excel 2010 :: Summary All Statement From 4 Worksheets And To Summary All Total ICC

Dec 23, 2013

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?

View 2 Replies View Related

VBA Code To Validate Date When User Enters A Date

Jun 13, 2014

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

View 4 Replies View Related

User Input Date Not Kept In Date Format

Mar 29, 2014

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‎

View 2 Replies View Related

Sum Horizontal Data In Vertical Summary Where Data Matches?

Jul 9, 2014

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] ..........

View 5 Replies View Related

Summary Of SUMPRODUCT Data

May 10, 2009

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?

View 6 Replies View Related

Keep Data Summary Always Visible

Sep 28, 2006

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 Related

Summary Of Data Without Zero / Null

Mar 4, 2008

eg.: 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.

View 4 Replies View Related

Compare Date: Compare The Date From The User Input And The Date Listed

Jan 18, 2007

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..................................

View 2 Replies View Related

Create A Summary Tab From Excel Data?

Apr 28, 2014

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 Related

Summary Of Data From Multiple Worksheets

Jan 11, 2010

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

View 2 Replies View Related

Summary Sheet Of Data From Other Workbooks

Nov 14, 2008

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?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved