I had a working macro before i added an extra row for another entry (No. of Hours spent). The entire data collection calculates the total price of a project (called PSR) in another cell underneath. The macro is suppose to move the entire data into another sheet to keep a history (in Sheet2). I've attached the file containing the workbook.
I have a worksheet where in column B I have a vendor name, column E I have a date value and in column AF I need to calculate a due date based on 7 working days for vendor A and 14 days (not working days) for vendor B. I've tried to write an IF statement but can't do it. Can anyone suggest the right combination of IF/AND statements that would do this? I attach the workbook should my useless attempt at explaining my problem be unclear...
I have a conditional formula that highlights dates red when it meets a certain criteria. The file named MS Working, the conditional formatting formula (formula below) works as it should with no issues. The other file MS NOT Working, the conditional formatting formula (formula below) has stopped working as it should. What I did? I inserted a new column to the left. Where the MS Working file has only 1 column to the far left, the MS NOT Working file now has 2 columns to the far left.
It seems as if the formula adjusted itself when I inserted the new column; however, its not working.
MS Working Conditional Formula: =AND(TODAY()>B3,NOT(ISODD(COLUMN())),B3<>"",OR(C3="",C3=0),B3<>0)
MS NOT Working Conditional Formula: =AND(TODAY()>C3,NOT(ISODD(COLUMN())),C3<>"",OR(D3="",D3=0),C3<>0)
I have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. How can I insert escalation date so as that it would deduct non working hours from it.
I am using the worksheet change routine for conditional formating of more than three items.
The problem is that it only changes if I manually type within the page and does not update if the cells are updated via formulas.
To explain a little more, I have three sheets that I combine into one to help find the best rate from three companies using the following formula:
=IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" All",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4),FIXED(PMI!C4,2)&" PMI/UG",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" PMI/GE",IF(AND(MIN(PMI!C4,UG!C4,UG!C4)=GE!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(UG!C4,2)&" GE/UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,FIXED(PMI!C4,2)&" PMI",IF(MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,FIXED(UG!C4,2)&" UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=GE!C4,FIXED(GE!C4,2)&" GE","ERROR"))))))) Please feel free to condense this if you like.
So when I update a reference on one of the first three sheets, the cells update on this sheet but the cell colors never change. This is the code for the worksheet change
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel1 As Range Dim Rng1 As Range
On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error GoTo 0
The loop is putting the correct formula into the cell - but THEN... - I wish to (within the loop and before it moves on) check if the value is equal to OR greater than 1 - if it is, make it a value of 1 (this '1' is then used at the bottom of the column of data to give a total). I need to do this before it moves onto the next cell. What am I doing wrong? Is it because once it puts formula in the cell, it then moves down - do I need to select the cell again first? As I had originally posted on this site - link: url]http://www.excelforum.com/showthread.php?t=641970[/url]
The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.
Is this due to a security setting or missing code line items?
Below is a code which I added to my 'Quick access toolbar' (and which is based off a hidden workbook). I use this macro to fulfill tasks to regular excel spreadsheets that are not macro enabled, however, I can't seem to get this macro to work for other workbooks. How to modify my code to work for other spreadsheets.
It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.
I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.
I have this macro which sits in the workbook module:
Code: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Static OldRng As Range
On Error Resume Next If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then OldRng.EntireColumn.FormatConditions(1).Delete OldRng.EntireRow.FormatConditions(1).Delete
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code: Sub Send_Mail() 'this sends an email that sends a text message
Dim OutApp As Object Dim OutMail As Object Dim MyButton As String Dim strTo As String
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
I've got a semi-recorded macro to format a report I run, and towards the end it deals with sorting, subtotalling, formatting text etc. The main function of the macro is to split a large report into several smaller reports defined by whichever manager name appears in the relevant column. The smaller reports need subtotalling by 2 different columns so I have the following macro code :
For some reason this only subtotals by the second column and not the first. I tried swapping them over so GroupBy1 then GroupBy2 but it's still the same. Anyone got any ideas why this might be? I appreciate it's not the smoothest code I could use but I don't know enough about code to do any better.
I've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.
The macro I have now is:
Sub Copy() Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy End Sub
I have a macro to export two worksheets and save as, but the save as part will not work, it just exports to Book1.
Ive checked the directory and everything is fine.
I want the value of a cell in one of the exported worksheet to be included in the save as name.
I'm just wondering if the code needs to somehow specify the worksheet to get the cell value from or will it default to the first worksheet. but either way my code is still not working and i can't tell why.
Should i just create a seperate macro for the save as?
I got a problem with a macro i'm working on. I got column D which contains text as "Figures", "Toys", "3DS", "PS3" etc. I also got column N where i need the macro to display "Toys" if it's a toy, figure, etc; or "Games" if it's "PS3", "PS4" etc.
I'm using the following code, to also select only blank rows (so to ignore row 1 which is table header). Problem is that the result shows only "Games".
I'm having a problem with my macro .However, if i use the following script:
If i am correct all the rows with a value less than 1 should be hidden right? However, some rows are hidden when they shouldn't be.
Im working on a quotation with different products in it. And the rows with a 0 in it should be hidden and that's pretty much working. But it also hides some of the subtotals(which i dont want) and some stay unhidden.
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
I found the following code elsewhere on the net, and have attempted to use it in my workbook to change the names of all 31 worksheets when a certain cell's (C7) value changes. The code looks good to me, so I cannot understand why it is not working. I am not getting error messages, just no changes to the sheet names.
I believe this may be due to the value of C7 on each sheet being dictated by a formula, so the value is not being changed with the sheet active, but indirectly with a formula that pulls the value from a cell on another sheet, and then adds.
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:
I have constructed a macro, (with lots of help from the fine people on this site), and have distributed it to several people in the company. The macro was written in Excel 2003, and runs perfectly on all the machines running Excel 2003, but persons using Excel 2000 are getting a runtime error, variable not defined.
Is there a trick to getting a macro to run in both versions of Excel?
i have an excel doc. with three sheets... i may start that macro on any sheet any cell the 1st thing that macro does is to go to sheet1 and after running the whole code it calls another macro... macro2
now macro2 runs in an infinte loop and runs evry 20 sec. the problem is every 20 sec the cursor comes back to sheet3.. i want the macro to return to the place where the user was working.. best wud be that the user can work seem lessly...
I cant seem to figure out why the macro in this workbook quits working after any error.
I can Break from the error but then the macro ceases to function at all. I am forced to close the workbook and re-open it.
FYI This macro writes to the registry. Simple text values only. Example: Retrieving data. Its currently generic in format. "sLogin" is a user entered name "TargetValue" is a selected cell value (activecell).