The part in green will count the number of entries for the name Johnson & Freedman LLC perfectly fine. However when i add the last part in red i receive a #Value! error.
Col. W is formatted as General and has a data validation for the user only to choose Pass or Fail.
I need to flow data from the Download tab to the Disb08 tab based on the date, that column F of the Download tab = "Outgoing Money Transfer" and that the 4 digit number in row 2 of the Disb08 tab is matched against Column G in the Download tab. When all those match, then the value from column J of the download tab would flow over.
I want to compare 2 lists in separate sheets based on multiple criteria and delete the duplicates
Sheet 1 - new list in column A:E Sheet 2 - old list in columns B:F
So here is what I need: the macro should generate single IDs made of Sheet 1 Ai,Bi,Ci,Di,Ei cells for each row i to the end of the list + generate single IDs made of Sheet 2 Bi,Ci,Di,Fi
If . Evaluate (Sumproduct (IDs made of Ai,Bi,Ci,Di,Ei from sheet1) & Sumproduct IDs made of Bi,Ci,Di,Ei,Fi from sheet2) >1 then delete the entire row in Sheet 2.
This will leave me with only updated items (rows) in Sheet 2
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.
using various keywords, and I haven't been able to find a solution that works. It would seem SUMPRODUCT is what I need to use, but thru all the combinations I've tried, I still can't get the syntax right. The problem is: I have dates in col A, and the word "Holiday" in col D. I need to create a formula that counts the number of instances of the word "Holiday" in col D, only where the date in col A is =DATE(2008,1,1))*(A11:A45
I have a workbook that has several macros. When the workbook is saved, it cycles through and kicks off several of them.
This works fine, no errors. I have another macro that saves and then emails the workbook. When this macro runs, the same set of macros is launched due to the save method however it errors during one of the macros.
Basically what's happening is that the one macro (derivecountry) unprotects a sheet and inputs a data value. This macro works fine when run independantly but when it runs as a result of the email macr, the sheet does not get unprotected and the error results.
Here is the code line that runs to unprotect but doesn't always work
We are an office furniture company and we want to hold a master list of all stock. The columns we require are:
Product ID Product Description Product Grade Product Price Sold
The Sold column would be a YES or NO answer, and based on that I want the data to split into two additional worksheets, one with all the unsold items and one with all the sold items. So really I need to know how to split the data based on whether the line has a YES or a NO in the sold column.
The data needs to update instantly so if I change a product from unsold to sold it then comes off the current available stock tab.
2. I need grab data from each of the files for a rolling 30 day period from the date specified in the sheet. The data needs to go onto the same sheet in Excel.Example: B2 on the "Settings" worksheet says 02-14-2014. So take data from 02-14-2014 minus 30 days (01-15-2014.txt) all the way up to today and consolidate it onto an existing worksheet called "Data."
3. If the date in cell B2 is changed, overwrite the data that was already retrieved and replace it with the new import from the new date.Example: B2 is changed to 01-31-2014. Now take data from 01-31-2014 minus 30 days and overwrite what was already imported in worksheet "Data."
I need to show hidden columns based on the date I entered. For example, if I entered "1/1/1990" on a1 as the starting date and "4/30/1990" on b1 as the ending date. I want Excel to show the columns that are covered by the date, thus it shows Jan, Feb, March and April. How do I do that? Here's an example attachment. In here Sheet 1 is the starting point, the highlighted cells is where I enter the date. the Result sheet shows what I want Excel to show me when I have a date entered.
From this forum I've borrowed code and reused in my own CapEx approval form. The form is originated and goes through three people for approval. Everything seems to work ok on the form but it always forwards to the first approver rather than the next in sequence. I cannot see where the code is wrong.
This macro should copy all data from all worksheets and past them into the sheet named "Consol" however It is not looping and only pastes the one sheet.
Sub LoopThroughSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Formula FinalRow = Range("A65536").End(xlUp).Row Range("A2:U" & FinalRow).Copy
I have an Excel 2007 workbook with over a dozen user forms and plenty of code that I have mainly obtained from the web and tweaked to suit my needs. I am a novice with VBA, so not really up to writing anything but quite simple code.
My issue is I have some code set to fire on Workbook_Open that will save a versioned copy of the workbook, to the same network folder that the original resides in, with an incrementally increasing file name. the file is stored on a network, but access should not be an issue as I have full access and have no problem saving to this folder, also the event works fine up until I shut down Excel. I have also tried saving to My Documents to avoid the network issue, same result, worked fine as long as Excel is not closed, fails if I do.
It all works fine until I close down excel completely and re-open, where it then fails to work. There are other events happening in the same Workbook_Open sub that still work fine each time, so the sub is firing on open, but this one event fails. I get no error message at all, just no new file copy created.
The workbook is essentially doing the job of an Access database (I know even less about Access), I have a user form as a main menu and various other forms for various data entry and reporting tasks. I am exiting the w/book via a cmd button on the main menu (I've deliberately restricted users control, as many are not very pc literate). I have conducted numerous trials consisting of running the code from the VBA window, closing w/book via cmd button WITHOUT accepting the std save option and re-opening from Explorer window, closing w/book via cmd button WITH accepting the std save option and re-opening via Explorer and all worked perfectly over many sample runs. But when I closed Excel totally (Not just the w/book), created a desktop shortcut and opened from there, that line of code just doesn't seem to do anything, no error or hang or anything. The only way I can get it to function again, is to re-save the w/book (As either a new file with code edited to suit, or overwrite the original), and keep Excel open whilst only closing this w/book. It then functions perfectly again on opening.
I have enclosed below the Workbook_Open sub and some other subs that append to a user log on opening, these work fine all the time. I enclose the others in case they may have some bearing, as they are also fired from the Workbook_Open sub and show no issues at all.
Any suggestions gratefully accepted as I am struggling. As mentioned above, the ONLY part of the Workbook_Open sub that fails is the line "ThisWorkbook.SaveCopyAs newFileName". I have even added "MsgBox "The new FileName is: " & newFileName" immediately after it, and that displays new filename correctly. It seems to me to be hingeing around the SaveCopyAs event, but I don't want to assume that, being the novice I am.
I'm finding that my add-ins that worked perfectly on Excel 2003 are not functioning on Excel 2007. One of the main issues seems to be loading any UserForms that use the RefEdit control. I get a message saying:
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
What i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
The below code works well if it is on the proper sheet, if the active sheet is not active then it errors- Why? I think I have properly qualified the path to the desired sheet I wish to search on.
Error msg: Activate method of Range class failed
And how do I handle this?
Set Ws = Workbooks("ViewRenameDeleteFiles.xls").Sheets("Item Record List") What = InputBox("Enter the Name You are Searching for its Record#", "Item Name Searching On")
I have written the following code that is supposed to be running on opening a work book. The macro is being called via Application.Run "CreatePT" which is in the this workbook code in a Sub workbook_open().
The code run on its own is fine with no errors... It only bombs out when its called via application.run then its a run time error 1004? ...
I made a new class to make other code less redundant, but it isn't functioning. The class has a "workbook" and "worksheet" member, and these can be accessed through properties. The problem is that the properties don't actually seem to return anything, and no data can be accessed through them.
Private mActiveWorkBook As WorkBook Private mActiveWorkSheet As Worksheet
Property Get wSheet() As Worksheet wSheet = mActiveWorkSheet End Property
Property Get wBook() As WorkBook wBook = mActiveWorkBook End Property
'Sets active WorkBook Sub SetActiveWorkBook(ByVal wBook As String) Set mActiveWorkBook = Workbooks(wBook) End Sub
'Sets the activeWorksheet in the workbook. Sub SetActiveWorkSheet(ByVal wSheet As String) If mActiveWorkBook Is Nothing Then MsgBox ("Invalid WorkSheet selection - WorkBook not defined") Return End If
Set mActiveWorkSheet = mActiveWorkBook.Worksheets(wSheet) End Sub
uDate.wSheet. Range("A1").Value = "foo" ' Expected result - set Cell A1 in sheet testTab = "foo" ' Actual Result - nothing
Dim st As String st = uDate.wSheet.Range("B5").Value MsgBox (st) ' Expected results - bring msgBox of values of cell B5, this cell is not empty ' Actual Result - Empty Message box comes up
So - its fairly obvious to me that something is wrong with the properties. The members themselves are not null, I have verified in the debugger that the class members refer to actual sheets/workbooks, but the properties don't like passing anything out and show as "variable not set" in the debugger. How can I get this to work?? It works fine when I do not use the class, like such:
Dim wB As WorkBook Set wB = Workbooks("Book1.xls")
Dim wS As Worksheet Set wS = Worksheets("TestTab")
Dim st As String st = wS.Range("B5").Value msgBox(st)
But I would like to get the class and properties to work to save clutter elsewhere.
Range Name = AssignDt RefersTo: =OFFSET('Raw Data'!$W$2,0,0, COUNTA('Raw Data'!$A:$A)-1,1)
I perform a routine in VBA which deletes unwanted rows of data from my worksheet. The problem occurs if row 2 happens to be one of those rows. It not only deletes Row 2, but it also deletes my Named Range.