The way the code works right now is that I have it run as a few loops. it will first loop through the first worksheet in the array and delete what I need and then when that loop finishes it will run two macros that I have added in, then it will loop to the next worksheet in the array. right now it is only 2 sheets but eventually it will be many more. just working out the code for now. What I would like to do is in the second loop. my second macro in the code formats each page and, what I would like to do is add a date column to my code. I need to start at 1/05/2014 and then for the next iteration in the loop add 7 days.
I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing------------------------- Date Range
Bucket Amount Percent Start Date End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
I have 10 comboboxes - all require exactly the same list. Rather than having to copy the list 10 times in the coding - and changing the combobox name from listcode1, listcode2 etc, is there a loop code which I can add to do this for me??
Private Sub Userform_Initialize() 'Empty txtdate txtdate.Value = ""[code].....
In this spreadsheet I am working on for calculating vacation time based on accrual rate. how to add pay period dates automatically based upon the first on entered. I have done this before in a personal budget spread sheet and this is what I have in that. First cell I enter the date Second and following cells I have the formula "=IF(DAY(E$8+7)>7,E$8+7,"")" Everything works perfectly. No matter what I put in the first cell the following cells will adjust and display dates 7 days later from the previous cell.
I am trying to do the same thing except pay period dates are 14 days apart so I put the same formula except change the "7's" to "14's). The problem I am having is that ONLY the first cell displays the correct date, the second displays a blank cell and the remaining displays "#VALUE!". What the heck is going on? I cannot figure out why this formula works in one circumstance but not the other.
I am not sure what I am doing wrong, but what I am trying to do basically is what ever I enter in the first cell, I want all remaining cells in the date column to enter the date of 14 days later. Example, I enter in the first cell 3/27/09, in the next cell I want 4/10/09 and in the next cell I want 4/24/09 to be entered and this all the way down.
it has various headings - but the ones I am stuck on are below.
BADGENAMEDATE Issued
He has set up a basic vlookup so that typing in his badge number also will type out his name. However, he also wants the current date to come up every time he does this.
As the names and badge number don't change and have no difference from row to row, how can I get it to date stamp it with a different date each time?
The code is supposed to project review dates 1 month, 2 months and 3 months after the start date, but the first "cell offset" line on is projecting the next day instead of next month, its driving me nuts!!, the others are working fine
Code: Sub reviews() Dim cell As Range
For Each cell In Range("L1:L200") If IsDate(cell) Then cell.Offset(, 10).Value = DateAdd("m", 1, cell.Value) cell.Offset(, 11).Value = DateAdd("m", 2, cell.Value) cell.Offset(, 12).Value = DateAdd("m", 3, cell.Value) cell.Resize(, 10).AutoFill cell.Resize(, 11) End If Next cell End Sub
I have a spread sheet that shows the dates items were shipped. When trying to work out the value of parts shipped in a month using SUMIF I get the value zero, I believe this to be because (obviously), there are multiple dates of ships during the month, I therefore tried ignoring the day of ship in the SUMIF but this did not work, the date format is DD/MM/YYYY.
I'm trying to limit the information people can put into my format and therefore to ensure they put in a the correct date etc i'm wondering if it is possible to have a calendar pop up to click on the date for a particular cell?
I'm having great difficulty adding hours to dates in Excel. In cell A1 I have a number which is calculated from a formula, it will normally return whole number values between 1 and 100. In cell A2 I have a start date and time i.e. 14/2/07 06:00:00 in cell A3 I want to add the number in A1 as hours to the date in cell A2. I am using the formula in A3 as =A2+TIME(A1,0,0) it seems to work okay for numbers that don't take the date onto the next day but if I try to add 24 hrs or 48 hrs for example the date still stays the same.
Engineer name, attended site(date), report received(date), commercial/heating and bonus(gbp)
Engineers will only get a bonus if they send the reports in 1 day after attended site date for commercial, and 2 days after for heating. If the report is received past this window, they do not get a bonus!
Weekends do not count as a day!
This should be on one row per engineer. I then want to see how much "John Doe" is owed in any given month...
I am having a 2000 records of date and time in excel (see the below example). My query is I want to add 6 hrs to each Cell and accordingly the Date should get changed for e.g see the B Column after adding 6 hrs the data should look like this. I want an excel formula (don't want VBA Code) ...
How can i leave Cell D3 etc blank untill i have inserted the end date it will look tidier when i do my full spread sheet and also is there a calender on a toolbar i could put on my spread sheet for easy inputing of dates.
I have developed a user form in VBA which is used for entry into an excel worksheet. From this data, I need to do the following:
1) Create 2 output files for Job data entitled "J" and another file entitled "S". The "J" and "S" is captured in a field in the data. I need to hit a button that allows these 2 output files to be produced, so I need to 'loop' through rows of the data to do this/
2) In addition to 1), the user needs to be able to 'select' the date range they would like to see 'J' and 'S' data over. This will be based on a field called "Startdate'. How could I do this with the button above-can I parse an input value to a form and select the 'J' and 'S' files on this.
I have a column with dates and times, and an associated column with data for all the dates and times. I want to add every hour of every day to the date/time column, but want the data associated with the times to stay in the row next to the correct time. How would I do this? In the attached file, i want to make coulmns A and B look like Coulnds E and F. I have hundreds (if not thousands) of rows, so i need a way to do this easily. Also, the times are not all evenly spaced or exactly on the hour. I dont need the times to be exactly on the hour or spaced out evenly, but i would like to have at least one time from every hour.
A B C D E F (B+C-D-E) Date Opening Stock Purchase Sales Consumption Closing Stock 17/7/2012 10000 5000 1000 2000 12000 17/7/2012 12000 2000 2000 1000 11000 18/7/2012 11000 0 1000 5000 5000 18/7/2012 5000 10000 3000 0 12000
Note : I want the above result as shown below datewise but no duplication of date but summing up of column C,D and E which is in above.Pls note that this is accounting question and also plz note that Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
Results :
A B C D E F (B+C-D-E) Date Opening Stock Purchase Sales Consumption Closing Stock 17/7/2012 10000 7000 3000 3000 11000 18/7/2012 11000 10000 4000 5000 12000 (B=F)
if I could get the formula in excel ,since this is the sample data as I do have a numerous & huge data like shown above datewise where I need to add C ,D and E but Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
I found this script which is very similar to what I want to do but I am not sure how to finish modifying it.
This script compares every cell in Column B to Z on Sheet1 with Cell A1 on Sheet2, if it matches it will copy the active cell in column A and past the results to column A no Sheet3.
I am the consolidator for a specfic Excel file to be converted into CSV. In order for this file to be uploaded into our database properly it needs to be in a specific format and certain values must be entered if other cells have been filled in. At this point I have locked the formating so users cannot change it and I have created a Macro for blank cell rules.
The Macro is to verify that cells are not blank if the value in A is not blank. At this point it works as it should, for row 4. However, I would like to Loop this Macro throughout the entire worksheet.
The Macro I have created is (minus several columns for simpler reading purposes):
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Worksheets("JE FILE").Range("B4").Value = "" And Worksheets("JE FILE").Range("A4").Value "" Then MsgBox "You must fill in Customer." Cancel = True
The first (SS1) is where my raw data is populated.
The second (SS2) is my template.
In the first spreadsheet, I have a macro (button) that opens up SS2. Then in SS1, I also have another button that I click and it 'transfers' the first line of data to SS2.
In SS2, I have a save Macro, which basically saves as per the name in Cell A1.
What I would like to do is to loop the process, i.e. when I click the 'Transfer' button in SS1, it will open up the 'template' spreadsheet (SS2), copy the information across, save & close and repeat this for the remaining data in SS1.
Is there a simple coding to use that can loop this process for me?
I would attach the two spreadsheets for you to view but I am unsure how to upload on here.
I would like the macro to loop through rows and highlight the cell in column G if the value is at least 2x greater than the value in the cell in column D.
Right now I have a very long macro... but I'd like to change it so goes through all the rows I assign it to instead of writing many lines of code (lots of if then statements):
Sub Macro4() Range("G28").Select If Range("G28") > 2 * Range("D28") Then With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If
I'm trying to get this macro to loop and have no idea what I need to do do to get the macro to keep repeating until it gets to the last item in a list.
I have a list of addresses (some are 3 rows long, some 4 and some 5). And I want to Copy each one then paste special in the empty cell between it & the next address entry.
Here's the code for my simple macro that does one copy & paste ... I have to do each one manually & would like to modify the macro to go all the way to the end of my list.
I read that there are 4 types of loops (While Loop, For Loop, Do While, ??) ... don't know how to choose which one or how to use/format with my exisiting macro.
Sub Transpose_j() Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Sub..........................
I have a Macro that I use often - It cuts a Designated Row from my AMZ-GM Open Sheet and moves it to the first open row of AMZ-GM Sold and leaves Cell U in that row Copied to the clipboard.
I would like to add two more jobs to that macro but am having a hard time.
1. I currently start the process in AMZ-GM Open.xls by Control F to find the SKU Number for the item I want to move ( it is always in Column N ) - I then manually highlight the entire row and run the macro below.
I would like to add code to my macro to highlight the row that my SKU cell found is in.
SO.... I want to start my macro from a single chosen cell instead of a chosen highlighted row.
I need to start my macro from the chosen cell in Column N. I need to add code to Highlight the row that my active cell is in and then proceed with the rest of the macro.
I recorded a macro starting with a cell -highlighting the row but it records as a specific cell / row number. I dont understand how to make it based on what ever cell is shown as active.
2. I would like to take the copied cell text ( U ) that remains from the last command and insert it into an open Word Document ( Amazon Sale.doc) at the position of the cursor in the that open Word Document. I'm not finding much info on moving that cells text to Word.
Heres my current Macro --------------------------------------------------------------
Sub OpentoSold() ' ' OpentoSold Macro ' Macro recorded 2/1/2008 by Mike ' ' Keyboard Shortcut: Ctrl+q ' Dim objLastRow As Range Dim lastRow As Integer
I have the following code i want to add to the code at that bottom:
With combobox1Data If .ComboBox1 = "" Then MsgBox "All location must be selected"
Current code is:
Private Sub cmdDone_Click() End Sub Private Sub CommandButton1_Click() Dim TheName As String, Response As VbMsgBoxResult TheName = ComboBox1.Value Response = MsgBox("Are you sure you want to remove " & TheName, vbYesNo + vbQuestion) If Response = vbNo Then Exit Sub Range(TheName).Offset(-2).Resize(Range(TheName).Rows.Count + 2).Delete Shift:=xlUp Names(TheName).Delete.....................
I'm using a macro to drag down a formula across a worksheet then again further down the worksheet which is working ok but I know there has to be a better way of doing it. At the moment every time I add a new column I have to edit the macro over and over and its getting out of hand. I know there must be a way of rewriting the macro into a loop but my skills are obviously still new.
Here's the macro:
[Code] ......
This continues over to column AO so far then I drop down a few rows and do it again:
[Code] .....
I think I need to set variables that set the row and column each time. maybe a "drag formula one column at a time from row x to row y until column header is empty then move to the new row and repeat"?
I want to loop through all files in a directory and run a macro named "Main" in each file. The file that contain the loop-macro will stay in the same directory as the files I loop through.
