Ok my opening Date for an account is for example 200204 (that is the way it is written in the database for all accounts YYYYMM) Now what we want to achieve is to have a macro that fills out a column in a spreadsheet taking into consideration the opening date until it reaches lets say 200601.
so this is what the result should look like
A
1 200204
2 200205
3 200206
.
.
X 200601
how do I let the Macro know this is a date so as to add a month until it reaches the final date. I've managed to create a loop but I know it probably isnt the best way to write it
Dim lCount As Long
Dim lNum As Date
lCount = 0
lNum = Sheet1. Range("D2").Value
Do While lNum < 200601
lNum = lNum + 1
lCount = lCount - 1
Just wanted to know if I could have a range or ranges which are split. The current range i have is N5:AV6. I need to miss out every 6th cell so am looking to do a number of ranges like this - N5:R6, T5:X6, Z5:AD6 etc etc. If this can be done could you show me how I would need to lay it out?
i'm trying to get my code to work. Basically my code opens up workbook and extract the relavant data into the main workbook. Previously Its just a single For loop and it works perfectly after changing to double for loop its stuck.
Sub RunCodeOnAllXLSFiles() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim rCell As Range Dim lCount2 As Long Worksheets("Data").Select Range("a2:ac65536").Clear Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook..................
In column B is a list of dates, which are broken out by fiscal year (FY=7/1/ to 6/30). Column C is a list of dollar amounts. Cell F3 is the query ‘start date’ Cell F4 is today's date, which will be the formula: =today()
Using a date range of F3:F4, I'm trying to get each cell bordered in red to query the dates in column B for each FY; then display the matching sum from column C.
For illustrative purposes I went and manually determined what the values should be displaying as of 7/22. Here are a couple of examples of how I am trying to get the formula to work.
Example 1 FY’08: Start date- 7/1 End date- 7/22 Searching cells B1:B94, cells B1:B13 fall within the start and end date parameters listed above. Sum of cells C1:C13 is $45,112.00, which should display in cell F6.
Example 2 FY’09: Start date- 7/1 End date- 12/13 Searching cells B95:B222, cells B95:B149 fall within the start and end date parameters listed above. Sum of cells C95:C149 is $150,873.03, which F7 should display in cell F7. How to I type up this formula; its way more challenging that I first thought!
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
I have a masive table of dates (the date is created via a if formula) what i need is so wheni enter 2 dates in 2 cells the system checks all the dates between the two specified and then returns the contents of them to a small area on the page.
Dates To Test 14/08/2026 19/08/2026
Results
14/08/2026
15/08/2026
16/08/2026
17/08/2026
18/08/2026
19/08/2026
Table..............
i dont mind using script or anything like that, i dont have much knowlage of it but do have coniderable understanding of other langages and usually figure it out :D
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
I tried to do this using a IF THEN formula but it doesn't work or more likely I am not writing it correctly or its not the right formula to be using. It's on the attachment.
I have a worksheet that has dates starting from "1/5/2004" to "1/18/2010". Each date is listed in column A.
I want to convert all the dates from MM/DD/YYYY format to only MM/YYYY and have it appear in column B.
The kicker is I need excel to recognize all dates that fall out between"01/01/2004 to 01/30/2004" only as 01/2004 and not 01/01/2004 or 01/30/2004 so on and so forth for each subsequent month and year.
I have a table in which some of the ranges have to be set to "0" (write "0" inside, not delete) when I click on a button. All the other cells expect these ranges are locked. For now I use the very long way, with the following VBA Code:
There are more columns for which I have to do that, and it goes until row 500+ .. I used "Register Macro" to find out this code, that's why I guess it's obselete.
Is there any way I can do it, at least write all the ranges I have to fill with "0" next to each other instead of every time selecting, then inserting 0, then autofill the rang with...?
I have a list of items in a pivot table where each item has a date. I want to see how many items occur for each month. I am trying to use the COUNTIF formula, but I'm having trouble figuring out how to write the criteria for the dates.
How do I write the criteria part to include the entire month?
i have an report in which i need to delete the entire row if the column contains values more than 250K, but the chanllenge here is; if any of the cells in the column contains a formula then the macro should not delete the row.....
if I have another instance of word open my macro will not work correctly. I am at a loss on how to figure this out. Also some times I get the error of virtual machine is not available when getting activedocument.name.
Sub TestTemp() Application.ScreenUpdating = False
Dim bname as string
bname = Range("B6").Value
Dim WdApp As Object, WdDoc As Object Set WdApp = CreateObject("Word.Application") WdApp.Documents.Add "C:TemplatesTest Letter1.dot" Application.Visible = True WdApp.Visible = True
in writing a subroutine that needs to ask the user
1) tax file number 2) Income 3) tax withheld
It then needs to use a function that i already made called 'incometax' that calculates 'taxpayable' and use that to calculate taxreturn (difference between 'taxpayable' and 'taxwithheld').
I then need it to output like this in a message box
Estimate for tax file number: ..... Created on ...... Total income: ...... Tax witheld: ....... Tax payable: ......Tax return: .......
The following code is supposed to write values from one file into another file - depending on the value of Current_Rider_Name. However, although the code runs and values are being picked up (this is checked through the line that is being commented out).
However nothing is being written to the file. Can anybody point me in the right direction? ...
I am trying to write a UDF that will go like this:
IF A1 is Purple then B1=A1 Else B1=A2.
This is what I have written so far and I cannot figure out how to get it to work:
Function SumIfPurple( Range As Variant, Range1, Range2) Dim SumAnswer If Range.Interior.ColorIndex = 39 Then Range1 = SumAnswer Else: Range2 = SumAnswer End If SumIfPurple = SumAnswer
I am trying to solve a a problem that I am having.
I have a list of projects in one column and start dates in a different column.
I want to create a dashboard that breaks down projects by month, quarter and year to date.
I have tried various formulas centered around CountIf. Bottom line is I want to review a column of dates and determine how many projects started last month, etc.....
How do I sumif the date falls between two dates in a quarter.
For example Date of Activity - CellBG43 = 10/10/2008
I want to sum the corresponding Cell BJ43 with cost associated with the activity if the date falls between "01/10/2008"and "31/12/2008". I tried =sumif(BG43,">=01/10/2008 and <=31/12/2008",BJ43), =sumif(BG43,">=01/10/2008 & <=31/12/2008",BJ43) and it didn't work.
I am making a spreadsheet using excel 2003 to calculate sales and tax. Column B is formated for date (1/1/2010). Column D,E, and F are formatted for currency with column D=Total Price, column E=sale price, and column F=tax. In column B I type the date the transaction took place and in column D I type total price. Columns E and F are automaticly filled in with the formulas for same. In column H,I J row 9 I would like to calculate 1st Quarter Total Price, Sale Price, and Tax. Row 10 would hold 2nd Quarter info and so on. I searched for info but can't come up with any thing that works.
In our program it is essential that only data is entered for the current week.
I need code that if a date is entered into "cell C1",that it will evaluate this value and compare and assure that it is between the dates in Range D1:E1 (upper and lower values)
If it is between these two dates, nothing happens and you can proceed to enter data.
If it is not message -Invalid date entered. Proceed? If "Yes" nothing happens and you can proceed to enter data! If "No", then the value in cell C1 is set to nothing and allows you to enter the correct date.
I need to be allowed to enter an incorrect date at times,to review and check previous entries, 99% of the time it must be between the start and stop date.
I have searched through a lot of other posts, but I can't seem to make the recommendations work for my given scenario. I have a sheet that compiles information for different dates (based on a query that is run by the user), and in column H I have a start time that is listed in this format "m/d/yyyy h:mm."
What I need to do is find all of the unique dates "m/d/yyyy" from this given range, and return them in column O.
For example, I will have the following information:
Here is an example of what I am trying to do. I have a date in column A on Sheet 1. In Column B I want to return the period for which that date falls.
I have created a list of Start and End dates for each period in Sheet2. The start date is in Column A of Sheet2. The End Date is in Column B. And the resulting Period is in Column C.
Basically if the Date in Sheet1 Column A falls in between the start and end date for a specific period. I want that period returned in Column B of Sheet2.
I am trying to figure out how to calculate how many permits were pulled by a specific company during certain dates (qtr). My column headers are (a)Date, (b) Builder. My dates are displayed as "Jan-06" but are entered as "1/1/2006".
How can I show how many permits were pulled by a specific builder during a three month period?
I have a problem for extracting dates of leave from the attendance. A sample WB is attached. In the attached WB, I want to extract date(s) on which leaves were taken.
I have a problem, basically im writing in the cell but it keep going along, i want it so when i press enter it creates a new line in the cell, so i can have two lines of text in one cell. do i have to do something in the text settings?
