Find Start Date And End Date In Range

Jul 30, 2007

This is a sub that uses the Find method to find a series of dates and copy them to another worksheet. The following error comes up: Object variable or With block variable not set. I have tried using a set command etc. but other errors end up coming up.


Private Sub CommandButton7_Click()

On Error Goto errorHandler
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (mm/dd/yy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (mm/dd/yy)")
If stopDate = "" Then End
'startDate = Format(startDate, "mm/??/yy")
'stopDate = Format(stopDate, "mm/??/yy")
startRow = Me.Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Me.Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Me. Range("A" & startRow & ":A" & stopRow).Copy _
Destination:=Worksheets("Report").Range("A1")
End

errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48

End Sub

View 9 Replies


ADVERTISEMENT

How To Auto-Populate Date Range (working Days Only) From Start Date And End Date

May 25, 2014

I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.

View 1 Replies View Related

How To Auto Populate Date Range (working Days Only) From Start And End Date

May 26, 2014

i am trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.

View 4 Replies View Related

Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays

Nov 15, 2008

First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...

View 7 Replies View Related

Calculate Next Due Date Based On Start Date Frequency And Current Date?

Mar 9, 2014

I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.

Example

Initial Date :- 1st of January 2014

Frequency :- every 5 weeks

Current Date :- 9th of March 2014

Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)

I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)

View 4 Replies View Related

Find Corresponding Date Range Based On Single Date And ID Number

Mar 28, 2014

I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.

I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8

Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014

What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1

In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014

View 13 Replies View Related

Find Date Less Or Greater Than Particular Date From Range Of Cells?

Dec 12, 2011

way to search and display a date from a range of cells based on less than or greater than criteria. For example I have following dates in column A:

A1: 2011/01/04
A2: 2011/02/01
A3: 2011/03/01
A4: 2011/04/01

I want to search for the date which is less than 2011/02/01 from the A column and display that date in B1 cell for example. How do I do that?

I want to do this without using any macros.

View 9 Replies View Related

Create Calculator To Show Start And End Date Based On Specific Date Provided

Jan 12, 2014

I am trying to create a calculator which will show start date and end date based on the specific date provided.

EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).

View 6 Replies View Related

Calculate Start Date From Finish Date Minus 14 Working Hours

May 7, 2014

I needs a formula (not VBA) to calculate the required start date.

I have to do a job of 14 working hours and this job must be finished on 05-may-2014 13:00

My working week is from monday u/i friday and every day I work from 08:00-16:00

At what time do I have to start the job to get it done in time.

The formula should give this result: 01-may-2014 15:00

View 3 Replies View Related

Automating Out Current Loan Period From Start Date And Today Date In IMPT Function?

Jun 12, 2014

Trying to automate the period part of the impt function

To calculate current value of loan i have the below formula below with the 3 being the current period

=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)

What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.

View 4 Replies View Related

Vbcodes To Select Start Date Then Begins Highlight Until End Date

Oct 28, 2006

i need VB to select the start date of booking and autohighlights the "blank booking slot cells" until the end date that is input in Excel. You can visualise this as a room booking system. The left most column is the dates and the first row represents the different rooms type. E.g. if one guest books the Twin Deluxe No. 123 room on 23 Oct 2006 until 25 Oct 2006. We wish the VB can essentially help us identify the start date then begins highlighting the three slots for 23, 24 and 25 Oct on the column for the Twin Deluxe Room 123.

View 4 Replies View Related

Selecting Entire Date Range To Be Lookup Based On Start And End Dates

Mar 6, 2013

I have an excel sheet where I would like a user to enter a start date and end date(say Feb 1, 2013 to Feb 28,2013). And these dates will be able to change to whatever month the user wishes to look up in a specific date range(column A has 365+ dates). This post is closely related to [URL]... but I cannot figure out how to get it to work from my case.

So working off the same worksheet, I have the beginning date in AC35 and the end date in AC36 in which the user enters. I would like the dates that fall in this entire month, including the beginning and ending dates to be referenced when looking for the specific day that contains a value I have in S35.

In other words, I have data in columns A through N. The dates are in column A and S35 is a value obtained from using Max(L185:L526) where L185:L185 is manually selected each time I want a different month. I don't want this to be a manual process of scrolling down the spreadsheet to get the next month.

View 12 Replies View Related

Calculate End Date From Start Date & Number Of Weeks

Jun 26, 2007

Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?

View 5 Replies View Related

Highlight-Select Start Date To End Date

Oct 28, 2006

We are doing a project on a booking system and wish to use VB codes to autohighlight the slots from the start date until the end date.

View 9 Replies View Related

Add Days Between Start Date And End Date?

Jun 2, 2014

What I am trying to accomplish is, to add days from the start date (daily) and to stop adding at the end date.

Example:
(Column A) Start Date: 1/9/13
(Column B) End Date: 1/8/14

View 6 Replies View Related

Enter Start Date And End Date?

Mar 12, 2014

when I enter a start date and end date, it should autopopulate the columns with the dates in the middle including start date and end date.

View 1 Replies View Related

Autofill From Start Date To End Date

May 26, 2006

I have one column( date) which I wanna set date range from VBA macro. The problem is I wanna use Autofill to fill from the start date to the specific end date, but with Autofill func I cannot do this.

I attached a sample to explain my problem. Please take a look and give me a hint to do this.

View 6 Replies View Related

Date Range: Reference A Single Date And Output Date Ranges

Oct 11, 2008

I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:

Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25

and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.

View 2 Replies View Related

Calculate End Date Using Start Date And Number Of Days Excluding Specific Days

Nov 22, 2013

I want to calculate the end date of my German courses. This is how it works:

A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.

Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.

Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.

The workday function on excel cannot do this and I do not have any programming skills to work with VBA.

Legend:
*LU = lesson units; 1 LU is 45 minutes

Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14

[Code] ...........

View 7 Replies View Related

Using VB To Find A Range Based On Todays Date And Todays Date +30

Aug 13, 2009

1 column in my sheet is a list of dates (affectively a calendar). I am trying to create a macro that will only display rows 1-5 and then current the current date and the next 30 days and hide all other rows.

The approach I have tried is to use “ADDRESS(MATCH(BS40,$B$1:$B$462,0),2)” equation to find the cell with todays date in it (Cell BS40 contains todays date). This was repeated for todays date +30. The problem I have is that although I now have the cell address’s located I cannot reference their values in a range.select

What I want to do is somwthing like this:

View 2 Replies View Related

Find First Date Within A Range?

Sep 8, 2009

I have a table that shows across the top row date headings for year and month. So it would show like this, 2008/01 2008/02 2008/03 2008/04 etc to year ending 2009/12
I need a formula which will tell me the first date that a customer started transacting. Under each date heading is a count of transactions for each year/month by each customer. So, I need to search for the very first transaction number and return the date.

View 2 Replies View Related

Find Out If Date Is Between Range

May 19, 2006

if I have 2 dates 21/05/2006 and 23/05/2006, how can I find out if 22/05/2006 exists between them

View 4 Replies View Related

Find Date In Another Range

Apr 11, 2007

I have a formula =WORKDAY(TODAY(),-1) which will give me 10 Apr 07 in C1of sheet1 today. I am trying to find the cell in column A of sheet2 which has the date value equal to C1. However, my below macro keeps telling me object variable not set at the last line.

Dim s1, s2 As worksheet
Dim tdy As range
Dim today As Long

Set s1 = sheets("Sheet1")
Set s2 = sheets("Sheet2")

today = s1.range("C1")

Set tdy = s2.range("A1:A65536").find(today, lookin:=xlvalues)
tdy.Activate

View 9 Replies View Related

VBA - Find The First Matching Date Within A Range

Feb 19, 2009

In a row on the spreadsheet I have a range of dates from 19/09/2008 to 19/06/2008 mainly with a weekly interval.

I want to incorporate into the macro a find function so that it can determine the first occurrence of a specific month and return the row number.

View 9 Replies View Related

Possible To Use The Find Method Using A DATE RANGE?

May 11, 2006

Is it possible to use the Find method using a DATE RANGE? For instance, If I have two input boxes; One, a beginning date and the other an Ending Date.

Is it possible to use Find to search for all dates that are >= strBegDate and <=strEndDate?

View 9 Replies View Related

Find Next Date In Range From Input

Apr 6, 2008

have a range of dates in a column i can match an input date with this code where the input date is in cell H20 (eg 04/04/2008)

With ActiveSheet
Range("D:D").Select
Range("D:D"). Find(What:=Range("H20"), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

End With

the problem is that not all the dates in the range are consecutive and there may be the same date more than once eg
01/04/2008
03/04/2008
03/04/2008
05/04/2008

the problem is if the date in cell H20 (inputted by user) does not exist in column D, it all goes wrong.

what i would like to achieve is a way of incrementing the cell date by one day until it matches a date in the range, in this case if 02/04/2008 was entered 03/04/2008 would be picked up.

View 3 Replies View Related

Find Date Range Based On Value In Cells?

May 14, 2012

I have one excel sheet. In the first column there are various Project Codes and in the first row there are various date. Cells in the matrix contains 0 or 1 value. If value is equals to 1 that means the project was on hold during this months and if the cell value is null of 0 (Zero) it means the project was active. For a project in a particular row there are several times a project went on hold and now I want a date range showing the project's hold duration. If project went on hold more than one time the result should also give different range for each time when project went on hold.

follow the link to view the excel file.

http://www.excelforum.com/attachment...3&d=1336994800
[IMG]http://www.mrexcel.com/forum/data:image/png;base64,
iVBORw0KGgoAAAANSUhEUgAABAAAAAMACAIAAAA12IJaAAAgAElEQVR4nOy9e3gT153/P99fd7vd77fxV+3udtO06fr3PL

[code].....

View 8 Replies View Related

Find Smallest And Largest Non Zero Date In A Range

Aug 13, 2008

I am having some data running into 60 (C:BJ) columns and 200 rows. It is the record of dates query raised and answered with refernce to query number in the A coulmn. I have zero value as entry for the unused cells in this range due to some constraints. I need to find the smallest and the largest non-zero date in this range for analysis purpose. I plan to use the difference between these 2 dates to calculate turn around time for a query.

Problem is to find the smallest and the largest non zero date within range C6:BJ6 which would be used as input in the Networkdays formula.

View 9 Replies View Related

Find Begining And End Of A Date Range To Copy

Oct 28, 2008

I want to save myself some time at work, that's why I want to create an automatic sheet in Excel using VBA. I need to create files with monthly reports of data.

My project looks like this:
1. Gather information from user (Month & Year)
2. Create a file which name is based on Month & Year
3. Select data sheet
4. Find date range to copy (RowBegin, RowEnd)
5. Copy nad Paste required data into new file (point 2.)
6. Delete rows, formated in certain way
7. Find bottom of all columns and insert row with all formulas
8. Format all sheet acording to requirements

I was looking over MrExcel.com and I know how to do all the points besides point -> 4. Find date range to copy (RowBegin, RowEnd)

I got a column "D" with about 5000 cells for now and the sheet is updated daily. In those cells I got dates formatted like this:

D
4012 14/02/2006
4013 21/02/2006
4014 15/03/2006
4015 15/03/2006
4016 17/03/2006
4017 07/04/2006
4018 07/04/2006
4019 20/04/2006

What I don’t know is how get in VBA two variables – range of row numbers describing selected month (for example March) - from (RowBegin) & to (RowEnd), in this case RowBegin=4014 and RowEnd=4016, which I can later put into:

RowSub = RowEnd - RowBegin
Range("A”&RowBegin&”:A”&RowEnd).Copy Sheets("March").Range("A1:A”&RowSub)

View 9 Replies View Related

Start Date And End Date

Apr 5, 2007

I have the following code now that cbanks helped with before this question was locked and no Norie this exact question is not posted elsewhere....

View 9 Replies View Related







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