How To Return Data With Specific Date In Macro
Jun 1, 2014
I have my tracking data sheet in my master file.
I wish to create a macro code to only retrieve the data on the file last modified date with the specific date.
For example like this :
I wish when I click on the List button, it will pull out the file that is from 28/5/2014 till 30/5/2014 which I have input in B6:B7
I have my code like this but it will pull out all the file in the folder:
Code:
Dim iRow
Sub list()
iRow = 11
Call ListMyFiles(Range("C4"), Range("C5"))
End Sub
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
[Code] ........
View 1 Replies
ADVERTISEMENT
Dec 14, 2013
Basically, it is a scheduling of approving or rejecting of request dates. whereby, i will receive the request dates from Google doc form and then i will extract the data from web into the excel. The data i receive will include the ID, Date request and Remarks.
With the Information in hand, the data will then be "plot" into a schedule (which is a year calender that i have created on excel) base on the ID requested from the web and the ID in the excel.
After plotting, i also want to check if the number requested date exceed the quota set PER DAY, if it exceed, then i have to reject, if its not then approve.
So here comes the problem.. After all the dates have reflected on the calender, I want to get the information from the calender result(which is whether the date is approve or reject) back on to respective ID and Dates requested.
I have attached a example excel for clearer view of my explanation.
View 6 Replies
View Related
Jan 31, 2014
I have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
View 3 Replies
View Related
Jun 15, 2012
I have a list:
Item Code - Description - Quantity - Date
02099 - Blue Widgets - 500 - Jul 1
02099 - Blue Widgets - 200 - Jul 2
02099 - Blue Widgets - 300 - Jul 5
02099 - Blue Widgets - 500 - Jul 9
What formula can I use that returns the date (Jul 5) once the Widgets reaches 1,000 pcs?
View 7 Replies
View Related
Jun 14, 2008
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
View 5 Replies
View Related
Nov 15, 2013
I have a workbook with several sheets of data - it's basically names with a series of assessment data. Each name and selection of data is in its own row.
I want to build an additional sheet with a search function/drop down box that wil allow any name to be searched for/selected and return that specific name/all/specific cells in that row. I believe this could be done either using VLOOKUP or a Macro but I am only a beginner with either of these things.
View 7 Replies
View Related
Jul 14, 2008
ColA contains Purchase Order (PO) numbers and I want to return the department number or project number (which is contained within the PO) in ColB eg.
A B
PO # Department/Project #
AP001234P1234 Want to return everything before the 'P'
PR004444P4444 Same as above
TR2008-1234 No need to change
6501P1234 Want to return everything before the 'P'
I was using IF function but there are too many variables then I thought about the 'P' but not sure how to go about it?
View 10 Replies
View Related
May 12, 2013
I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.
Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6
Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0
The formula I am using is:
=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5
Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.
Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.
My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?
View 2 Replies
View Related
Mar 6, 2014
Is there a way to sum data up to a specific date?
I have a large data table, which includes various values throughout the year, if i want to look at the total value up until say 31/01/2014, but my data goes up to 28/02/2014, is there way to do this within the sumifs function?
View 3 Replies
View Related
Dec 15, 2006
I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.
I have two objectives that I am trying to reach:
First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date
Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.
If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.
I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.
Start date is column E
Finish date is column F
Owner is column L
View 10 Replies
View Related
Dec 1, 2006
how do i get the previous days working date in VBA, how can i get VBA to determine wether we are in the week, and how will i get it to know its a monday to retrieve fridays date?
View 3 Replies
View Related
May 21, 2014
I have this spreadsheet where in there's a Raw Data. Along with is there's a different tab for different user, is there a way that if a certain user choose a date it will pull up the data for that date in the Raw Data.
What formula should I use? and how should I do it.
View 7 Replies
View Related
Sep 10, 2012
I am trying to do a validation against a date.
Can a cell have a calendar button - for user to choose a date, and also have data validation? (so far I've played with validation, haven't found how to include calendar).
I would like to validate that the chosen date is at least TODAY+14, but also is a Saturday - is there any validation which will provide this?
View 8 Replies
View Related
Jun 1, 2007
What I understand is that balls are drawn in each dated lotto draw and the numbers of the balls thus drawn are entered into a spreadsheet against the actual date of the drawing - and so forth and so forth for each successive drawing.
Here is my version of the question:
If I want to look up the LAST date that a specific ball was drawn, the array formula that daddylonglegs provided works 100%, but when I replace the MAX in that formula with MIN (to get the earliest date??), it ALWAYS responds with 00/01/1900 with the cell formatted as a date, or simply 0 if it is formatted as a number.
I made sure that the ranges did not include empty cells and still it gives me that answer.
I attach the same workbook with daddylonglegs' formula in column D and my "tries" in column F.
View 9 Replies
View Related
Jan 18, 2008
I am trying to use a function to search for a particluar value (e.g., ssn) in one column and return the "InDate" in another column. I thought this would be easy, but I'm having considerable trouble. When I do manage to get a value returned, it's the wrong value. I need to return the most recent "InDate" for the ssn that I search for. The button on the " Lookup" sheet is to sort the InDate column descending so that the Excel function will start with the most recent date when it searches. I've tried Lookup and and just about every other similar function with no luck. I originally wanted to search for a ssn and return the information for the last 5-10 most recent visits, but gave up on that long ago.
View 2 Replies
View Related
Jun 14, 2013
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken
Cow
Donkey
Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
View 3 Replies
View Related
Aug 22, 2014
I would like to average data between 0600 and 2200 hours, between two date ranges, such as July 1st and July 31st.
Capture.JPG
View 3 Replies
View Related
Mar 7, 2014
I have a lot of rows of data in this date and time format "2/29/2013 10:00:25 AM" and I would like to remove only the time and leave the date
View 2 Replies
View Related
Mar 8, 2014
I have data in sheet 1 like below
Custid loc city
123 us newyork
124 uk wales
876 in mumbai
Sheet 2
custid newcustomerid
123 756
124 394
Outputsheet:
Custid loc city
756 us newyork
394 uk wales
876 in mumbai
So it will check sheet1 custid with sheet2 custid. If both matches it will replace custid with newcustid.if cust id not exists it will load sheet1 recprds as usual.
View 1 Replies
View Related
Nov 30, 2007
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}
But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}
SUM by both a specific month, and a specific year from a single date field?
View 3 Replies
View Related
Aug 31, 2009
I am looking for a macro which will take specific data from one sheet to the 2nd. I have attached the sheet. The Input data in the 1st sheet and the consolidated dat ain the 2nd one. Kindly assist. In the Final Sheet:
The Description column shld contain the data from B7:C7 and the B10:C10 together in one cell and that shld be copied till the end
The Description (Design steps) and Expected Result coulmn shld take data from the Test Step Description and Expected Result. And the Step Name shld be counted automatically till the last input. Test Name column shld be the 1st woksheet name.
View 4 Replies
View Related
Feb 22, 2012
I've been using this macro to pull data from multiple files, but instead of it creating a new tab, how can I have the macro pull the data and save it on a specific tab name(ex. Comp Stores). Here's the code I have currently.
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
SetCurrentDirectoryA szPath
End Sub
Sub Combine_Workbooks_Select_Files()
Dim MyPath As String
[code]....
View 1 Replies
View Related
Apr 19, 2013
I have set up a macro to clear data in COl A from row 2 onwards. I need to amend the code to clear the data in the following columns from row 2 as well
Col C to I and Col L to O
Sub ClearData()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
With Range("A" & i)
.ClearContents
End With
Next i
End Sub
View 4 Replies
View Related
Oct 25, 2011
I'm trying to match the data in a column from one excel file with another column of data from a separate excel file. If there is a positive match, I would like it to return a cell data on the same row in the following column on the first excel file. I understand that this can be done with vlookup but because the data in separate excel file contains commas and thus I can't get a true match.
To visualize what i'm trying to illustrate, i'll breakdown into steps.
I would like to match the data below in excel file 1 to excel file 2.
Excel file 1
83883
89342
83603
84445
434961
Excel file 2
83883
89342, 541879
83603
522354, 84445
83985, 434961
Lets imagine that there is another column of data next to the list of data in excel file 2. If there is a positive match between excel file 1 to excel file 2, i would like to return cell next to the data in excel file 2 on the same row.. to the cell next to the data in excel file 1.
This is difficult as I have a column with few thousands of such data.
View 9 Replies
View Related
Aug 20, 2013
I need a Macro that will search all of the cells for specific text, and delete all cells containing the text. I prefer to make a list of the specific texts and run macro to find all of them and delete all cells containing the text. I need to be able to add some more text into the list in future.
View 9 Replies
View Related
Aug 28, 2009
I have two workbook having 8 worksheet each. Every day I need to copy data from previous day worksheet and paste it on new workbook with same sheet name.
For example :-
Workbook1 is having sheet named aa, gg, tk .....
Workbook2 is having sheet named as same as Workbook 1
Now, I want macro to copy data from Workbook1, sheetname 'aa' and paste the same in workbook2 in sheet name 'aa' and likewise
View 6 Replies
View Related
Jul 28, 2006
how I might 'trigger' a macro to run when a certain cell was filled with any value at all. I have the macro working fine (it's a simple copy and paste special) but I want it to run automatically when the cell A20 has a value in it.
View 9 Replies
View Related
May 22, 2007
I'm trying to do is definetly simple to a lot of you, but I'm struggling on how to get started. I attached a workbook that clearly shows my work! I'm almost there but not quiet. Basically the user will be able to make a slection that will be used to lookup values from a different sheet. Each selection triggers a ranking number at the bottom (red pattern) in the worksheet. Now I would like to capture all scenarios and display the ranking.
View 4 Replies
View Related
Feb 23, 2010
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
View 11 Replies
View Related
Apr 29, 2014
What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.
Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.
Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
4) repeat requirement 3 till no more Scenario's
View 14 Replies
View Related