Find Date Within Workbook

May 28, 2008

I have a workbook with multiple sheets (one total and one for each pay period, 27 in all) to track time and attendance. From the main worksheet where my totals are, I want to be able to enter a date and then jump to the worksheet that date is found on.

For example, if I want to enter time for June 25, 2008, instead of searching for the right worksheet to find that date, I would like some way to type 6/25/2008 (US date) and enter, and that entry will take me to the worksheet that 6/25/2008 is found on so that I may then key my data. My dates are formatted in US style with the first date hard typed and the rest are =Date +1.

View 9 Replies


ADVERTISEMENT

Open Workbook, Find Value Add Date To Row

Dec 7, 2006

The following code is supposed to open a workbook, find txtVinput.value and put todays date (now()) in column C.

it doesn't generate any errors, but it doesn't add the date either. I'd post the file, but its just over the limit and I don't want to get into trouble (50K)

The whole prog is a userform that the operator enters a receipt number and it posts to another workbook, with the date. The operator also has the option to insert a date when the paperwork is returned. This is the part that is causing me trouble.

Sub InsertReturnInDatabase()
Application. ScreenUpdating = False
Application.EnableEvents = False

View 9 Replies View Related

Open Workbook, Find Sheet That Contains Cell Value From Active Workbook

Jun 20, 2008

I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...

'Dim officen As Integer
'Dim thiswb As Workbook

officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"

Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................

View 8 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 Starting Date And Fill In The Following Months Until End Date

Mar 4, 2013

I have collected some data on economic factors for different countries. Unfortunately, the dates when I started to calculated my economic factors are different for each country (due to the data available to me).

What I would like Excel to have done is to take the date when I started to measure for e.g. country A (D3 ie 30/06/2007), copy it into column "I" (for country A, it's cell I3) and fill in the following months in the rows below (with always the date of the last day of a month) until it reaches 28th of Feb 2013. Then, it should go up to the next country (country B) take the starting date (D4, ie 31/07/2007), go to the last entry in "I" (ie I71) and paste the date in, fill in the months until 28th of Feb 2013, do the same for country C and so on.

I have started to code a VBA but I am unfortunately a beginner in VBA and totally stuck at the moment. My VBA code does paste in the months but for some reason, it also changes the starting date of the first month.

Moreover, I tried a workaround for the fact that Excel doesnt know when to stop; ie I introduced a "monthdiff" variable which should calculate the number of months between the starting date (which is variable and unique for each country) and the end date (which is always 28th of Feb 2013). At the moment, it only does this for country A.

VB:
Set rng = ActiveSheet.Range("I3" & Cells(monthdiff, "I").Address)[SIZE=4][/SIZE]

I have tried to make this dynamic but have been unsuccessful so far.

Spreadsheet with data&code is attached.

VB:
Sub Macro1()
Dim mainrange As Range
Dim rng As Range

[Code].....

View 3 Replies View Related

Find Pay Date From Date Sold (Friday Of Following Week)

Feb 17, 2014

If a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.

View 3 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

Referencing Then Subtracting A Date From Another Workbook From Today Date

Jun 6, 2014

I am trying to take the year from today's date then subtract away a date from a referenced date in a separate workbook. Then I want to put this result into an IF statement. For example if the result is between the number 1 and 4, display 1, if the result is between 5 and 9, display 2, etc. Right now, I'm simply trying to reference the separate workbook for the date and subtract that from today's date, but I'm stuck because I only get the date's serial number back.

View 1 Replies View Related

Formula To Find Last Available Working Date Before End Date

May 22, 2014

I need a formula thet will look at the last date of the absence and calculate what would be the last working date in the given financial yaer.

Example

Abcence Start Date 21/08/13
Abcence Due End Date 04/11/14
Financial Year Start Date 01/07/13
Financial Year End Date 30/06/14

View 13 Replies View Related

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 View Related

Find The Equal Date Or Next Less Date

Jan 29, 2010

What I have is the project name's in B5, B6.....B117

In C5 I have a start Date of the project

D5 Calculates the if it is over or under time.

I only enter dates in the (red Accent 2 80%) what i call a pinkish colour.

Now it seems to work if the dates flow from one cell to the next.

What I need is to work out a way to allow any part of the project to be completed (Could be at the end or middle) and it not give error and work correctly. I need it to basically look for dates entered and grab the date that is equal to the date entered in that row series or the next date down....

View 9 Replies View Related

Find Same Value In Another Workbook?

May 17, 2014

I want to find the same value in another workbook

In A2 I have the number 112233 I want to know if this number is found in the other workbook in the entire workbook to search it in all the worksheets . if it is found then true if not falls

View 2 Replies View Related

Find Value In Another Workbook

Oct 7, 2007

I found the error a little further down the line, actually a few errors conspired together. Sorry to waste time, Moderators please delete this if possible. I have no idea how to improve on the thread title.

Windows("Race Strengths.xls").Activate
Sheets(TLocation).Activate
TRRowCount = Range("J1"). CurrentRegion.Rows.Count
Stop
TheRace = Class & NewTrackClass & MeetDay & AgeRest & HorseRest
Set look = Range(Cells(2, "J"), Cells(TRRowCount, "J")). Find(TheRace)
If Not look Is Nothing Then
h = look.Row
RaceRating = Cells(h, "I").Value

Windows("PastTenStarts.xls").Activate
Cells(m, " AI").Value = RaceRating

Goto 155
End If

when I run the whole code without the STOP comand the corresponding ratings are wrong, when I run it and STop it and then continue with F8 the code produces the right results. Everything else is the SAME the only thing I change is I make the Stop a comment of not to enabel it or disabel it.

View 2 Replies View Related

Find A Specified Value In All Sheets Of A WorkBook

Apr 1, 2008

I have a short vb code to delete a list box value, but i now need to add a few lines to also delete the list box value IF it exists in cell J59 on a worksheet.

View 12 Replies View Related

Find A Text In A Workbook

Jul 28, 2009

I'm running this code to find a text in a workbook but i need to know if this code find nothing .

View 12 Replies View Related

Find And Use Data From Another Workbook?

Feb 9, 2010

Open the workbook called get_status.xls then run the user form. Enter 123456 into the textbox then click the Get Status button. I want to get info about the item from the workbook called object_status.xls which is on a network drive (known location) and is not open. Do I have to open this workbook to look for data in it?

So I have to find the matching text string, then get the value of the adjacent cell. If the text string is found in cell B5, then return the value from C5. Always look in the B column for the matching string. After the info is found, close object_status.xls and write the value to the label in the user form (lblResults) in the get_status.xls workbook. If 123456 does not exist in the object_status.xls workbook, then return "Unknown" to the user form.

View 2 Replies View Related

Find Cell From Another Workbook

Dec 6, 2013

I have to open another work book to get a certain cell with a value.

ex. in my source file (prod plan) i have columns A,B,C,D. in column B it contains Line1 up to Line10. In column C it contains Model and Column D it contains Quantity.

Prod Plan workbook

Sample:
A-----B-----C------D
---------------------
AAA--Line1--M1----87
BBB--Line2--L2-----45
CCC--Line3--X1----0
up to Line 10

I need to find all Lines (column b) as my reference to get the column D records using Excel Macro and paste or copy to another worksheet. Is this possible to use the IF to get the data from Column D or Looping. How to make this in Excel Macro.

Desired Result:

Line1|87

View 3 Replies View Related

Find Every Instance Of 0 Within A Workbook

Mar 27, 2007

I need a Macro that will find every instance of 0 within a workbook and then replace that value with an average of the values in the two cells above it and the two cells below it?

View 9 Replies View Related

Find All Lists In A Workbook

Mar 14, 2008

It's been a while since I've visited MrExcel, but I've been busy learning C# & ASP.Net & Visual Studio & SQL Server - work doesn't want too much from me :p

Anyway, I'm writing a procedure that plays around with lists in Excel (i.e. the type of list that would show up in CurrentRegion.
They're not connected to any outside data source, or as a pivot table - just columns of data that will have a header row in a different format than the data body.

Is there any way of cycling through each sheet in the workbook and identifying each occurrence of a list?

I've been playing with the code at the bottom of the post, and it finds the boundaries of each list providing (at the moment) that each list starts on row 1. There are other problems with it however.

And to identify the values in the header row? .....

View 9 Replies View Related

Find And Copy To Another Workbook

Feb 11, 2007

What I'm looking for is to copy information from certain cells, G9 G11 G13 G16 G19 G21 in book1 into A B C D F and K into book2, like so G9 = A G11 = B G13 = C G16 = D G19 = F G21 = K

I tried running the macro and copying and pasting it in but what i need it to do is to find the next empty row down and enter new data rather than copy over the old.
The second, although really before copying is I'd like to the data in G11 to search collumn B and find if there are any existing entries containing that data and, if so, maybe pop up a window that says "Entry alreadyexists" or even loop it round and enter it in G11 saying the same thing and doesn't copy anything, but if the data doesn't exist in G11 then data in all cells on book1 is copiesd as aforementioned

View 6 Replies View Related

Find Last Row In Non-active Workbook

Feb 11, 2007

I have a number of files which I am opening, manipulating and copying the result to a master workbook

I am attempting to use code like this

Selection.EntireRegion.Copy Destination:= Workbooks(Master). Range("A" & LastRow)

Is it posible to define the value of LastRow without making Master the active workbook?

View 3 Replies View Related

Cannot Find Link To External Workbook

Jul 31, 2014

I have a document (unfortunately I cannot attach it) in Excel 365 that each time I open it I get the usual prompt that it is calling an external link. As much as i'd love to hide the popup I need to find the link to solve to problem but I'm at a loss.

Simply breaking the links doesn't work for some reason, so I've tried updating the source to reflect the current file. No luck there.

I've searched the workbook for the name of the link in question, searching for all formula for name itself as well as variants of "[" and ".xlms".

I don't see anything in the name manager referencing that external file.

I don't recall if I copied from that particular document, I may have as it was a duplicate file. I tried looking over the cells I believe I copied from it but didn't see the reference.

I've installed Kutools and Bill Manville (MS MVP) FindLink Tool. Both of which says there is no external link.

I've looked at the compatibility checker which basically just says, yes there is an external link but give no insight on how to address.

I've tried a couple of macros, some of which crashed excel, some of which simply said that there was an external link but not how to find it. In my frustration I forgot which one that was but I'm about to try that approach again and look for that code.

external links.png

View 5 Replies View Related

Find Function Not Working In Workbook

Aug 13, 2014

I'm in a workbook, and I want to look up a name. CTRLF or clicking on the binoculars both bring up the expected dialog box, but when I populate "Find What" and either hit enter, click Find All or Find Next, nothing happens.

The only thing I can think of is that I created a macro to function in one sheet only, then saved this workbook as .xlsm. But that doesn't seem right.

View 1 Replies View Related

VLOOKUP To Find Data In Another Workbook

Mar 18, 2008

The table to the right is actually in another workbook, both workbooks will be linked. What I'm trying to achieve is a vlookup formula that will count the items in the left table that have numbers entered into them. (This table will be located within a different workbook).

View 9 Replies View Related

Find That Time In Another Sheet (which Is In The Same Workbook)

May 13, 2009

I want is when a user enters a time into a specific cell I want Excel to find that time in another sheet (which is in the same workbook) and give out the details of the cell which is next to it, see attached image file.

View 3 Replies View Related

Find Text From Cell Within Workbook?

Mar 16, 2013

I am trying to simplify process for the users of a workbook. This workbook has about 20 tabs and I would like the users to manually paste a value in C1 of sheet1 and have a macro button that will find that value in the whole workbook.

View 3 Replies View Related

Find Workbook With Specified Cell Value And Open?

Nov 6, 2013

I am needing a macro that will search all excel files in directory C: and open the workbook with cell F4 value equal to "Checklist".

View 9 Replies View Related

Find Then Copy And Paste Into Different Workbook

May 30, 2014

I am trying to find text in one workbook and paste it into another. I've tried a Vlookup, and now I'm trying a Find, and neither is working.

This is what I have so far...

Sub FindAddress()
Dim GCell As Range
Dim Page$, Txt$, MyPath$, MyWB$, MySheet$
Txt = "N.A.V."
MyPath = "T:01862a7R228 Reports2039067"

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

View 9 Replies View Related

Find Macro In Workbook Modules

Jun 24, 2014

Sometimes I will copy a macro to a different workbook and then modify it to do what I want instead of writing it new.

If it has a reference to a different macro, I have to go through each module in the "VBA Project" to fine it and see what it does.

I was wondering is there a way to fine a sub routine in a project without scrolling through each module to fine it? (without going back to the workbook and clicking on the view macro button)

Also is there a way to alphabetize each sub routine in a module so it can be found easier

I've started to group my subs better so I can find them easier, but there are a lot of old ones.

I just learned that I can name each module.

View 5 Replies View Related

SQL Use To Find Data In Another Worksheet In My Workbook

Dec 6, 2008

I've heard that you can use SQL to query another worksheet in your workbook, but can not find anything on syntax or how to get started.

View 9 Replies View Related







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