Add One Day To A Variable Date Using Vba

Sep 25, 2007

I am using an input box to obtain a weekending date which I then use to filter for rows of data after the weekend (to be deleted). The problem is that because the dates in the data contain time, when I filter, the data still includes the weekend date which I don't want to delete. I need to add one day to the imput date which can then be the variable date "weekend" used in the autofilter.

Dim dweekend As Date
weekend = InputBox("Please enter week ending date (mm/dd/yy)", "WeekEnding")
Selection.AutoFilter Field:=4, Criteria1:=">" & weekend, Operator:=xlAnd
Excel 2003 Windows XP

View 9 Replies


Date Formula With Variable Date & Comparison Operator

May 14, 2008

I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?


View 6 Replies View Related

Storing A Value In A Variable For Use At A Later Date

May 19, 2008

I have an Add-in that creates and formats reports for various users.

Within the add-in I have rules set to disable some buttons on a userform untill a process is run to enable them. I am doing this through the use of a variable - set as "0" untill such a time when the process is run that it will set the variable to "1". A function is then run to enable all of the buttons.

However, once excel is shutdown and re-openned - it resets the variable to "0" for no apparent reason. Is this normal? Is there a way around this - so that the Add-in remembers what the variable was set to before closure?

View 9 Replies View Related

Add Variable To Look At Date In Column

Jul 4, 2012

I have the following code and I am wanting to add another variable to look at the date in column E.

I want it to only pull out the ones for the current working week. How I can add this in? Preferably without having to manually change it every week?

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Not IsError(Range("B" & i)) Then

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

View 3 Replies View Related

Searching Date Range With Mm/yy Variable

Feb 10, 2010

I have vba DATE variable 'FirstDate'; I assign it from a cell with say, 01/12/08; How do I then use that variable to search range of dates, without using the day and searching by only month and year?

Currently, I get the error: "Run-time error '91'; Object variable or With block variable not set" at the point of With Selection.Find(FirstDate).Select

I have been going in circles many times on this type problem: managing clumsily to resolve in the past using: Format(Firstdate,"dd-mm-yy"), text-to-column, NumberFormat etc

View 9 Replies View Related

Match Date Passed To Variable

Dec 7, 2006

In my excel sheet, I have date values stored in cells from D7 ti IV7. in cells C8 to C100, I have a data validation which selects the values from cells D7 to IV7. When user selects a particular date in coumns C8 from the list, using my code, I am trying to search for the value stored in cell C8 within the range D7:IV7 using function Application.Match

While running the code by clicking on "Distribute Budgeted Efforts" command button, I am getting an error "Type Mismatch" and error is coming from code line number 27 which is "lInitial = Application.Match(lInitialdate, Range("D7:IV7"), 0) - 1"

My code is as below

Private Sub cmdEffortDistribute_Click()
Dim lCount As Single
Dim lStartDate As Date
Dim lCounter As Single
Dim lBudget As Single
Dim lInitial As String
Dim lInitialdate As String
Dim lInitialWeek As String

I am also attaching excel sheet which I am using here for reference.

View 5 Replies View Related

Pass Date In Cell To Variable

Jun 3, 2008

I am using this program in one workbook to capture the datevalue in integer from another workbook which i opened. But the program as it reaches the line x2=Datevalue( Cells(2,14).Value) gives a Type Mismatch error.

Public Sub find_date()
Dim x2 As Long

'I am trying to activate the last opened file by using workbooks.count
Worksheets("Sheet1").Cells(1, 1).Select

x2 = DateValue(Cells(2, 14).Value)

End Sub

Auto Merged Post Until 24 Hrs Passes;btw..the cells(2,14) has a date, formatted in the type of mm/dd/yyyy.

View 4 Replies View Related

Summing Amounts Corresponding To Variable Date Ranges

Oct 25, 2007

Need formula which can sum Amounts from varying Weekly time periods and the result be recorded in the appropriate month? I've attached a simple example of the way the output needs to look and a sample data table below.

View 3 Replies View Related

NET WORK DAYS Between 1 Fixed And 1 Variable Date

Apr 7, 2008

Im wanting to calculate then no of days between 2 dates. The first one is variable.

Date 1: (Contract start date) Either the date entered in a cell A1, or 01/04/2008 - whichever is later

Date 2: (Contract end date) Date entered in cell A2.

example 1:

Date 1 = 1/1/8
Date 2 = 31/12/8

In the above example i would want the calculation to work out the n o. of working days between 1/4/8 and 31/12/8

example 2:

Date 1 = 1/5/8
Date 2 = 31/12/8

In the above example i would want the calculation to work out the n o. of working days between 1/5/8 and 31/12/8

This will allow me to calculate the number of contract days in this financial year, even if date 1 was in last financial year.

View 10 Replies View Related

Append Variable Based On Date Ranges?

Oct 10, 2013

I have a reference dataset that looks like:

Adweek Start_Date End_Date
201201 05-Jan-12 11-Jan-12
201202 12-Jan-12 18-Jan-12

In the dataset I would like to merge the variabe Adweek, looks like:
Date Sales
05-Jan-12 $100
06-Jan-12 $110
15-Jan-12 $150

I'd like to get to here:

Date Sales Adweek
05-Jan-12 $100 201201
06-Jan-12 $110 201201
15-Jan-12 $150 201202

View 2 Replies View Related

Store Date Variable And Use To Find Cell

Jan 14, 2014

I am having trouble using the find function. I need to store a date as a variable and then find this date on another worksheet. The date is in the following format:


This is what I currently have which gives me a run time error 91:

Dim DateSearch As Date
DateSearch = Range("C3").Value

Cells.Find(What:=DateSearch, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

View 4 Replies View Related

VBA Code To Open File Using Variable Date?

May 29, 2014

I need a code which will open up a previous days file. I have the code to pick the correct file but I am stuck because the folders are multiple levels down to year then month.

Eg. Folder Name > YYYY > Month

So i need to have a code which can look in to the relevant folder based on the current date and then be able to open the file i need.

I.e. today is 29May so i need the code to open the 2014>May folder.

For 1st day of month I would need it to open last months folder I.e. 01June Open May folder

View 2 Replies View Related

Declare & Define Variable As Date Range

Nov 13, 2006

I'm fairly new to VBA and I need to define a variable as a date range
i.e. Period1 = 1Apr2006 to 29Apr2006, Period2= 30Apr2006 to 27May2006 etc

View 9 Replies View Related

Append Date Variable To Header/footer

Dec 4, 2007

I'm trying to append a date variable to the end of headers for let's say 3 worksheets in a workbook. Each worksheet has a different string value, and I want to add a date variable at the end.

For example, sheet1, sheet2, and sheet3 says "Country Analysis", "Regional Analysis", and "State Analysis", respectively. I want to add the month and date to that so it would say something like "Country Analysis December 2007" for Sheet1, without hard coding it. Essentially, loop through however many worksheets and add a date variable at the end of each header on the left side.

I know how to get the date variable (format(date, "MMMM YYYY")) and the code behind the headers.

View 9 Replies View Related

Collect Date From User And Pass To Variable

Sep 9, 2006

I have an existing macro which I am enhancing and I would like to have the user provide a date, either with a popup text box in a userform and then use a command button to hit OK and have the box dissapear, or in a calender which the date is selectable, and the date supplied go into a variable.

View 2 Replies View Related

Pass Date In Cell To Variable & Format

Sep 28, 2007

I'll get straight to the point:

where ( Date > 9/20/2007)

The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?

View 5 Replies View Related

Delete Rows Not Matching Date Variable

Mar 10, 2008

I have a need to call a sheet from another sheet, copy two pages from the newly opened, existing sheet to a new workbook, search one sheet of the new workbook for any entries that don't contain a variable I specify, which is the first four digits of the field, and delete them. The sheets represent a daily view of jobs (identified by the "RptDate" variable) and an entire monthly list of jobs, which I want to delete all of them not from the date specified in the variable "RptDate". The fields in column A contain the date in "mmdd" format as the first four digits, and I have that specified in the "B1" celll of the originating workbook.

I found some code in this link Search Column Delete Row If Value Found that looked good. I had to modify it since I needed to search for a variable (RptDate) and needed to search column A and start on row 8 of that column. My current code looks like this:

Sub Macro1()

Dim RptDate As String
Dim RptMonth As String
Dim iLastRow As Long
Dim i As Long
RptDate = Range("B1").Text
RptMonth = Range("B2").Text
Workbooks.Open Filename:=(RptMonth)
Sheets( Array(RptDate, "Total Database")).Copy
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 8 Step -1
If Left(Cells(i, "A").Value, 4) <> RptDate Then
End If
Next i
End Sub

It works fine right up to the "Rows(i).Delete" line. It errors out with a "run-time error 1004, delete method of range class failed" and highlights the above line. I know the fields I'm pulling the variables from are working right since it opens the correct workbook off of one of them.

View 6 Replies View Related

Count Unique Entries Within Variable Date Range ..

Apr 27, 2009

I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.

View 2 Replies View Related

Find Date & Pass Offset Cell To Variable

Sep 20, 2007

How do I use VBA to send the value of a cell, for example, cell J77 to the variable "mastervalue1"?

I have a spreadsheet that has a column with dates ( in the format "mmm-yy") and next to it a column that has values. I want to pass the value of the cell that is next to the date cell that contains today's month to a variable in VBA. Auto Merged Post;here's an example of my spreadsheet

View 9 Replies View Related

Subtact X Days From Date Variable & Retain Leading Zero

Jan 12, 2008

I am trying to insert a leading zero for single digits. A3 contains a date, i.e. 01/10/08

d = Range("A3")
dDay = Format(d, "dd")
'The result is '10'

dPrevDay = dDay - 1
'The result is '9'

dPrevDay = Format(dDay - 1, "dd")
'I anticipated the result of this to be '09', but it is '08'

How do I get the result of '09'?

View 7 Replies View Related

Count Unique Entries Within Variable Date Range

Jan 26, 2008

Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.

Goal: create a count of unique entries within a defined variable date range

I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.

I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.

It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?

View 9 Replies View Related

Creating Autofilter That Returns Records Based On Date Variable

Mar 13, 2013

I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.

If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.

Code to return records on the date:

Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate

This returns data, however if I slightly amend it to get records on or after the date:

Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate

It returns nothing. Is there a syntax problem in my code.

View 1 Replies View Related

Finding Latest Date In A Range Dependent On Record Variable

Sep 4, 2013

I have this table as my simple little database and I have to extract some info from it and keep track of dates and deadlines...



I have to get the following data, I need to lookup the last visit date out of all dates associated with a specific name in column "A".

This requires that I test an unsorted table for a value in column "A", i.e. "test2". Then determine which record has the latest date.

In other words, If I test for the value "test2" in column "A", I find I have 2 records, with dates ranged across "D4:F4 and D7:F7".

I need to return the latest date found in those ranges...

The purpose here is to write a function that will find a range of dates associated with a name in an unsorted table, from that range find the latest date, then add 90 days to that date and display the results....

I have been trying to employ a form of, =VLOOKUP(E3,$B$3:$D$11,MATCH($F$1,$B$1:$D$1,0),FALSE) and =INDEX(Table_Array,MATCH(Lookup_Value,Lookup_Array,0),Col_Index_Num) combined with "IF" functions, but to be totally honest I am COMPLETELY lost here....

View 9 Replies View Related

Date Conversion And Use A Variable Of Type Long As A Search Criteria

Jul 22, 2008

How to assign a variable of type Long to the below code.

1) Dim searchField as LOng
ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200=""" & searchField & """,$C$2:C$200))"

2) ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200= searchField,$C$2:C$200))"
1) code failed because I want the variable searchField to have a data type of LONG.

2) code failed because it seems like excel thought the search criteria is of same searchField.

How to convert date to general format and store it in a variable of data type Long. I.E if I convert 3/15/2008(data type of Date) to data type of General, it should be 39522. What is the VBA code for doing this conversion?

View 9 Replies View Related

Set Variable: Object Variable Or With Block Variable Not Set

Jun 4, 2007

I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"

Sub test()
Dim StartRng As Range
Dim Buffer As Range

Set StartRng = WorkSheets("Sheet1"),Cells(1,1)

ActiveCell. CurrentRegion.Select

Buffer = rngStart.CurrentRegion.Copy

' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
End Sub

View 9 Replies View Related

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

View 4 Replies View Related

2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set

Feb 20, 2009

I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...

The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.

View 9 Replies View Related

Summing Variable Values Across Variable Sheets In Multiple Columns

Jun 27, 2014

I need a macro that will create a sheet at the end of the workbook.

Sum data from a variable amount of sheets and display that data on the created sheet.

Here is a step by step:

Starting on sheet 5.

Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.

Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.

(Note: The data would also need started on row 4. Everything above row 4 is headers)

Here is a small example:

13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1

I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).

After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.

So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).

View 2 Replies View Related

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

View 3 Replies View Related

Excel 2007 :: Object Variable Or With Block Variable Not Set Error

Feb 28, 2013

I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.

But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"

How to set the file which got opened from the folder to wbk variable.

Sub dataintoonesheet()

Dim i As Integer
Dim jk As Integer
Dim j As Integer

Dim rowstart As Integer
rowstart = 3

[Code] .......

View 9 Replies View Related

Copyrights 2005-15, All rights reserved