I attached partial file so you can see what i mean. I pasted only a few combinations cause the file was to big and i wasnt able to attach it...there are 142506 combinations . But you can see what i mean. A VBA or a macro on the worksheet will do the job?
I have a spreadsheet titled "PMIX" In Columns A:F Column F has Week Ending date. Each week, I need to update this, i.e. delete the data from two weeks ago and then add the new weeks data. What I am thinking is I enter a Week Ending Date in H1, then I would like VBA to delete all rows where it has the data in H1. I am hoping for a delete function as opposed to a clear so I can shift all the rows up.
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code: for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row) if x = y then *storedRow = rowNum end if next rowNum *
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
I have a range (D5:BU5) that I will have various dates in each cell. At the end of the row is a percentage block. It has the formula count=(D5:BU5)/70 which gives me a percentage complete. The fun begins where some dates are based off annual, semi-annual, and quarterly. What I want to do is be able to delete cell values so they are not reflected in the final percentage. I know there is a marco that deletes the entire row but that is too much I just want a target cell.
I need to have a User input a date via an InputBox, and then based upon that date, delete rows based on values in Column G. For instance, if a user inputs 12/25/2006, I need to delete all rows that have the date that is less than 12/25/2006 in Column G. There will never be more than 500 Rows of data, and there is data in Columns A thru Z
modifying the following from deleting rows older than 365 days to delete rows older than 12 months. So I can keep a rolling year by month.
Sub DeleteOldSR() Dim x As Long Dim iCol As Integer
Application. ScreenUpdating = False
iCol = 7 'Filter on column G (Create Date)
For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1 s = Cells(x, 3).Value If s Like "Closed" Or s Like "Closed w/o Customer Confirm" Then If Cells(x, iCol).Value < (Date - 365) Then Cells(x, iCol).EntireRow.Delete End If End If Next
Application.ScreenUpdating = True
End Sub
I tried modifying the IF statement using month with the following but it seems to delete all months < and is not year dependant.
If Month(Cells(x, iCol).Value) < Month(Date) - 12 Then
One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.
I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.
Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
I wondering if it's possible to create a code to have a popup where the user can enter a date in mm/dd/yyyy format and then all rows containing that date will be deleted.
I have tracker, where Cells in Column F have dates or are blank.
I am trying to run a macro to delete all rows when the following criteria apply:
for all cells in column F, if date < 1-Oct-2013 then entire row should be deleted if cell is blank - nothing should happen This is what I got so far - but it is not working...I have entred the date "10/1/2013" in cell AA2 but macro should always check against that date (not variable) so it might not be needed.
Sub PART3_Delete_old_team_members() Columns("F:F").Select Selection.NumberFormat = "m/d/yy;@" Dim endrow As Integer On Error Resume Next endrow = Sheets("Global Team List").Range("F900").End(xlUp).Row Searchdate = "AA2"
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)
MS Query can only hold 16 joins and some other restrictive requirements. Has utilized Stored Procedures to execute a query and have the results returned to Excel? Query in Excel seems very limited.
I need to call a Stored Procedure in a SQL Server from VB, but do not have the minimun Idea about doing it....Could you help me ?...I made a lot of attemps copiying code from other treads and scratch it, but so far results = none.
Server name: VAIO/SERVIDOR DataBase: CEDRO I´m using Excel XP.
I'm trying to open multiple files, pass them into an array and then work on them but the code below doesn't work (receive a Run-time error '9' message)
Public aFile As Variant
Sub MultipleFiles ()
MsgBox ("Please select multiple book files (holding CTRL as you click on flies) before you click open")
I am opening a workbook using a macro. I need to run a macro in the workbook I am opening when it opens. I have created a variable containing the workbook name but don't know how to use it to run the macro.
Dim WBName WBName = ActiveWorkbook.Name Application.Run _ "'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'!aStartProcess"
'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'! needs to be replaced by the name stored in WBName
aStartProcess is the name of the macro in that workbook I want to run.
When I use the SUMIF(S) function and a criteria range that has numbers stored as text (eg: '394) the unequal operator (<>) doesn't work.
=SUMIFS(B:B,C:C,"<>493") doesn't work
I'm able to get the salary of employee 493 by using "493" instead of "<>493", but it seems that with the unequal operator Excel starts treating this ID as number. The only work-around I found so far is to use a wildcard e.g. "<>*493" or "<>493*". Excel then treats it as text again, but this is dirty workaround. What happens if an employee with an ID e.g. 1493 shows up!?
"<>'493" or something doesn't work unfortuantely..
I am using the following code to run a Stored Procedure Query. It works perfect bringing back 4 columns of data. But is it possible to make it so that it only brings back 2 columns of data. I need the 2nd and 4th, which are named "DateOf" and "Gen", respectively. Someway to include a select command?
I posted here, without luck: http://www.mrexcel.com/forum/showthread.php?t=367809
I have a spreadsheet which has over 30,000 records. One column is a row of numbers stored in a General format. Some of these numbers are however stored as Text and have the following Error Check:
"The number in this cell is stored as Text or preceded by an apostrophe"
As a result, when I try to import this sheet into Access, the numbers are not imported and appear as blank cells in the Table.
Is there a way either manually or programatically, format the column so all cells in that column are stored as a number and do not have the issue of being stored as text?
I'm creating my first stored procedure ever and it looks relatively good so far. The problem is with passing the parameters, more exactly:
Arguments are of wrong type, are out of acceptable range, or are in confilict with one another
The essential part of the stored procedure looks like:
Code: CREATE PROC GetUserAuthForApp @User varchar(7), @application int AS
while the essential code calling it from VBA looks like:
Code: Dim strConn As String 'Connection string to SQL Server Dim strSQLtoExecute As String 'SQL query string to execute Dim oConn As ADODB.Connection 'Object for connecting Dim rs As ADODB.Recordset 'Object for recordset Dim cmd As ADODB.Command Dim prmUser As ADODB.Parameter Dim prmApplication As ADODB.Parameter Dim stProcName As String 'Stored Procedure name
Set wbABC = Workbooks.Open("C:ABCwithMacro.xls") Call abcMacro 'this macro is stored in ABCwithMacro.xls 'error - sub or function not defined on 'call abcMacro'
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1 Restricted 5 etc
Example of data ie. a1 b1 c1 Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
