VBA Code To Set Unknown Range Based On Current Date In 1 Column
Jul 21, 2009
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies
ADVERTISEMENT
Jun 20, 2013
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
View 12 Replies
View Related
Nov 18, 2013
setup:
Columns: c)6:00 AM, d)6:15 AM, e)6:30 AM
Rows: 4)tech1 Phone, 5)Tech2 Phone, 6)Tech3 Phone
What I am tying to do is to select the column that matches the time, for instance anything before 6 AM would select Column C, betweeb 6 and 6:14 still selects column C, 6:15 to 6:29 selects column D, Etc
View 2 Replies
View Related
May 16, 2014
The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 Replies
View Related
Oct 26, 2009
I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
View 14 Replies
View Related
May 19, 2009
i can't seem to figure this one out.
I attached a file showing how i am trying to format data (the second tab).
The big issue is the counting to format. (the formatting itself is obviously easy.)
The number of columns is not always standard so i need to count for it.
the number of rows in each "room number" is also not always standard, which means i also need to count for those.
If anyone has had experience trying to do something like this i would appreciate the help.
i know it is possible because i had one that someone had given me years ago, unfortunately i lost it in a hard drive failure...also years ago.
if it isn't obvious what i am trying to do, let me know and i will try to be clearer.
View 9 Replies
View Related
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
Apr 17, 2009
I know this is a simple question for all VBA droids...I guess the challenge is in how quickly I'll receive an answer. I hear the turn-around time on this board is pretty impressive. So here goes...
What is the VBA code for recording the current date without the date recalculating when the form is re-opened. I know the formula for automatic entry [=today()], but I need the date to stay the same. I can assign the date to any cell, so you may theorize any cell in the code. Remember...the trick is getting the date to stay the same once it's been automatically entered.
View 9 Replies
View Related
Feb 15, 2010
I want to schedule a print, for lets say every Sunday at midnight. I have managed to find some what of a solution by using the following vbscript and scheduling a print to default printer. Works perfectly if that is all I wanted.
Dim objExcel, objWorkbook, strFile
' Connect to Excel
Set objExcel = CreateObject("Excel.Application")
strFile = "C: est.xls"
' Open the Excel File for Printing
Set objWorkbook = objExcel.Workbooks.Open _
(strFile)
' Print to Default Printer
Set objSheet = objExcel. ActiveWorkbook.Worksheets(1)
objSheet.PrintOut
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
I do not want to print to an actual printer, therefore if I set my default printer as the document writer, the vbscript gets it as far as the "Save As" page. Is there a way I can add to this vbscript so it will save as well? Can I get it to save as "name"&[DATE], thereby having it save as a different name each time?
Can I change this vbscript to print to a named printer rather then a default printer? I am sure a PDF convertor may be of some assistance with this, but I do not have an PDF converter on my computer, and as this will be carried out on a computer that I will not have administration rights, I can not download one.
View 2 Replies
View Related
Mar 14, 2014
I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.
I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.
Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.
I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.
this was the macro that was created
Code:
ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
Range("A59").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"
Range("A60").Select
View 8 Replies
View Related
May 30, 2014
Future Date Sample.xlsx
I have attached a spreadsheet with a formula I'm looking for. I tried HLOOKUP, INDEX & MATCH, And others, but I was nowhere near what I needed.
View 2 Replies
View Related
Dec 14, 2011
I have a report that in column A shows a ship date. These ship dates may be "Previous","Same Day", "Next Day", or "Future".
I need VBA code that will look at the day in column A and determine if it matches the current date on the computer if it does then in column W in the same row I need the string "Same Day".
If its the systems date plus 1 day column W in the same row would state "Next day".
If its the systems date plus 2 or more column W in the same row would state "Future".
If its any date prior than than the current system date then column W in the same row would state "Previous".
View 9 Replies
View Related
Apr 29, 2008
I am having a problem which I imagine will be fairly simple to resolve but I can't figure it out!
Cell A1 contains a Date eg (01/01/07)
In Cell A2 I want to write a formula that says: Insert 'Warranty' if the system date is within 365 days of the date in Cell A1.
View 3 Replies
View Related
Oct 15, 2009
I need a macro that keeps only the top 25 rows in the current range and deletes the rest.
View 9 Replies
View Related
Feb 14, 2013
I need to work out how to get the last (or most recent) Friday date. Suppose today is 14/02/2013, then the previous date is: 08/02/2013. If today is 15/02/2013, then I just want it to be 15/02/2013 as it is Friday.
View 3 Replies
View Related
Mar 11, 2014
I needed a code that would input the current date and time in the cells in column N whenever changes were made to any cells in the row from columns A to M. For example, if I change a name in cell 6D, then cell 6N would automatically change to the current date and time.
I found a useful code on a forum (maybe here, don't know for certain) and modified it to suit my needs (see below). I am however now getting a debugging error suggesting that the second line that reads "Private Sub Worksheet_Calculate()" is causing an error.
View 11 Replies
View Related
Feb 24, 2009
I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).
View 3 Replies
View Related
Mar 9, 2008
I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell.
View 5 Replies
View Related
Jan 3, 2007
I am trying to get a function to return a date based on the string in the preceding cell. If the cell, say A1, has the string "Before 12:30", i want the cell B1 to put the date 5 days from now in the cell. If there is any other string entered then 6 days from now. I also need it to include the networkdays function.
Here's what i've got, messy i know but functions aren't my strong point. Also would i save file size if i did this in VBA? ....
View 9 Replies
View Related
Feb 4, 2014
I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.
1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …
2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?
View 4 Replies
View Related
Jul 13, 2014
Macro that will perform the following.
Search for the current date in a range of cells and then select the cell next to it.
View 6 Replies
View Related
Dec 23, 2008
I have tried to record a macro and change it to what I need but so far I have not been able to make it work.
I would like to have a macro that will auto filter on colomn 1 in the following way.........BETWEEN >= Today -10days and Today.
View 2 Replies
View Related
Aug 21, 2012
I have the formula for calculating a 13 week rolling average, but I need to be able to set goals in the future and still have the average calculate from current and not from future.
I have my dates set up in row 3 (these dates are all mondays, not every day) and my data in row 4. I need the 13 week rolling average to calculate starting from the prior monday and going back 12 more mondays for the 13 weeks. IE: This monday I'm running reports that the info will be entered into the previous week's monday.
Here is the formula that I'm currently using: =AVERAGE(OFFSET(K4,,COUNT(K4:BV4)-13,,13))
The formula works but will calculate the average for 13 weeks prior based on the last cell with data in it. It's calculating the average of the last 13 weeks of next year.
how to make this work without having to remember my weekly goals every week??
View 6 Replies
View Related
Aug 16, 2013
I'm trying to find a formula that will allow me to show a value based on whether certain dates have passed. This is for the purposes of calculating PTO hours and removing unpaid holiday hours from the equation.
I've got "slave" cells set up with the dates and amount of hours for each holiday, but I'm having some trouble.
I've been trying something to the effect of:
=IF(B4>B14,8 IF(B4>B15, 13))
Now, I can get it to show the "8" if I keep only the first IF function, but adding additional F functions like in the formula above only returns errors.
My goal is to get B7 to show the sum (through formula or manual entry) of C14-C21 based on whether B4 [=NOW()] is before or after the corresponding date in B14-B21.
I need to clarify any terminology or other cell values.
View 2 Replies
View Related
Jan 22, 2008
how do you calculate someone's age nearest today
View 3 Replies
View Related
Apr 3, 2008
I'm trying to link three cells lets call them C7, E7, and G7 to an NBA schedule. I'd like to have C7 Display the visiting team, E7 to display the date, and G7 to display the time of the game. Ideally I'd like these cells to change after the date of the previous game has passed to the next game. Is this possible? Would I need to create a separate sheet with the Visiting team, date of game, and tiem of game?
View 3 Replies
View Related
Apr 21, 2008
I am using the following formula:
=DATEDIF(F7,TODAY(),"Y")
I have a about 1200 rows with a column of student's birth dates. I need to convert the dates to ages and have them inputted in the next column Ages. I ran the code in the 'Age' column and it worked for the first birth date. But I do not know how to copy the code to the rest of the rows so it will convert every birth date in each row.
View 5 Replies
View Related
Jan 9, 2007
I need to compute the number of days that a given range of dates has in common with any given month. So an example might be:
Cell A1: Range Start Date (say it's 1/1/07)
Cell A2: Range End Date (say it's 2/15/07)
Cell A3: Month Indicator (say it's 2, meaning February)
Cell A4: Days of Intersection (should be 15 in this case, meaning that 2/1-2/15 were the days of February that were also in the range 1/1-2/15)
Cell A4 is what I'm trying to create the formula for.
I'm in Excel 2000 and am having trouble installing the add-on.
View 9 Replies
View Related
Jan 30, 2012
I'm trying to make the below two column selections into a selected range to clear out formulas/values in the range.
Current code segments:
ActiveCell.EntireColumn.Select
Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column).EntireColumn.Select
Is there a way to combine the two code segments into one selected range?
View 6 Replies
View Related
Aug 22, 2014
In one spreadsheet, I want to have a command button that will hide all rows where the date column (column A) shows a date older than one week from today. When this button is clicked again, all rows will unhide again. Preferably the Command button title would change to reflect whether it is on the hide or show cycle (for example "Click to Hide all older than one week" and then "Click to Show all events") .
View 9 Replies
View Related
