I have a fairly simple macro (below). I want it to do one more thing.....not run if the filters have not been changed. I would like to prevent the error that occurs if someone tries to run it and there are no filters active.
Private Sub AutoFilter_Remove()
Sheets("Data").Select
ActiveSheet.Unprotect "password"
ActiveSheet.ShowAllData
[Code].....
I've got a PDF file that I was hoping to convert into an Excel sheet, but it doesn't look like that's possible, xince I'm working with only Adobe Reader, not Acrobat. Anyway, I need to compare 1 column of this report to another report that IS in Excel. Is there a way to have a macro compare an Excel file with a PDF??
View 2 Replies View RelatedI'm having some difficulty with this one. I have found several posts on how to make a macro work through several rows, but cannot seem to figure out how to apply it to my macro.
Right now I'm having to write the macro for every line and it's a bear! Here's what I'm working with right now and it's ridiculous (I've actually got through line 51, but I'll only post a few here, for your sanity!)- there has to be a better way:
If Range("C9") = "" Then
Range("T9").Formula = "=IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))"
Range("U9").Formula = "=IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))"
Else
Range("T9").Formula = "=IF((T8+U8)>=180,0,(IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))))"
Range("U9").Formula = "=IF((T8+U8)>=180,0,(IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))))"
End If............................
I have generated macro, all I want is when this macro runs on different work sheets it should automatically select the active range based upon column A and copy the formula in the inserted new column B, please see the macro which will make it clear. Basically whenever it is copying a value or a formula it should be for the active range.
View 11 Replies View RelatedOR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"
Is there anyway around this?
Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?
Our system can create an SLK file showing customer open orders. It is ALWAYS in the exact same format. I created a macro to open this file, manipulate the data (convert text to numbers, convert text date to actual date, and a few other things) and then make a pivot table.
When I try to run it it gets hung up on the pivot table and the debug highlights the BOLD below:
Columns("F:L").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SOLIST!C6:C12").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
The RowField, ColumnField, and Page Fields are correct.
The QTY is the data that goes in the field.
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies View RelatedThe following formula gives me the error message #REF!
=INDEX(INDIRECT("R7"),MATCH(V6,D8:D11,1),11)
The problem I believe is in the INDIRECT("R7") as the following formula works
=INDEX(Well_AA_09,MATCH(V6,D8:D11),1),11)
The content of cell R7 is the text Well_AA_09 which is the name of a dynamic range I have created and pasted from within VBA into cell R7.
I've developed a spreadsheet that shows what errors people made in their spreadsheets. I compare cell A1 in spreadsheet 1 (original) against cell A1 in spreadsheet 2 (final), in spreadsheet 3. Spreadsheet 3 shows a "-" if the cell they wrote was OK, and shows the new, correct value if they made an error, as follows:
=IF('Final'!A5='Original'!A5, "-",'Final'!A5)
The problem is, this only works if I take the original spreadsheets, which contain formulae in each cell, and paste them into a new spreadsheet using paste values.
Is it possible for the IF function to search the formula in a cell, and not the value?
if i enter a value in 1 cell and want it to increase by a percentage how can this be done without having a sum column etc
View 9 Replies View RelatedI'm trying to convert some data from MLS to make it easier to work with statistically. The raw data regarding Bath count downloads from MLS in 2 columns; 1 column for Full Baths & 1 column for Half Baths (see Raw Data.jpg). I want to concatenate the baths to one column and, if there is a 1/2 bath, tack ".5" to the end of the full bath count.
The problem with my formula is that if it concatenates the ".5" to the full bath count number, it converts the format to text (see Current Formula.jpg). I know I can go through later and convert it back to number format but is there a formula I could use that would keep it in number format?
Is it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).
Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).
In same spirit what are best practices one can take like protecting formulas with password etc
So i got a few macros on a worksheet and one of them is simply :
Sub Pr_Removefilter()
ActiveSheet.ShowAllData
End Sub
Ive worked on the excel a couple of times, everything fine. Then it started crashing upon saving (after changes made that had no possible connection to the macro, on another sheet). After a few painfull trials and errors i located the problem to be this macro button. I erased it, created a new one with the EXACT macro and it works. Why?..
Is there a way to activate a Macro with a conditional in one of the cells? It's like this, I want that if the content of A2 changes to "True" then C2,D2 and E2 change to bold and the background color change to yellow. Is there a way to do this?
View 14 Replies View Relatedi have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
I've got a dataset of certain trades across different markets and traders, and on different days. The date of the trade is in column A, the contract ID is column D and the trader ID is column J.
Traders do not trade every day. I would like to insert a new row/rows for each date on which traders do not trade between their first and last trade, for each market in which they participate.
Is this possible using macros?
And, if so, then I would like to insert the high and low prices for the market on those dates from a different worksheet into the newly created blank rows. On this second workbook the contract ID is column A, the date is column C, the high price is column F and the low price is column E. I would like the low price to be inserted into column AJ on the original worksheet and the high price into column AK.
I have 2 workbooks:
-1st workbook, I am using a worksheet (let's call it workbook1/worksheet1) already running with macro. In this worksheet, there is a column "Protocol" where different protocols are named (some are repeated, not sorted)
-2nd workbook, there is a worksheet (call it workbook2/worksheet2) are just the list of description for each protocol listed in workbook1/worksheet1. For example: width, length, height
How do I get values found in workbook2/worksheet2 and use in workbook1/worksheet1 for each corresponding protocol?
A
B
C
D
[Code]....
There are four sheets (labeled A,B,C)
Depending on the contents in cells in Columns A and B (which have 1000+ rows), I want to change the background color of a referenced cell elsewhere:
For each row (lets say from 2-1000)
1. If "A" cell is blank -- referenced cell (address in columns C & D) should be colorless -- (as in Row 2 --- cell Z5 in Sheet "C" should be colorless)
2. If "A" cell has text AND "B" cell is blank, reference cell should be colored BLUE -- (as in Row 3 --- Cell F3 in Sheet "C" should be BLUE)
3. If both "A" and "B" cells have text, reference cell should be colored YELLOW -- (as in Row 4 --- Cell C5 in Sheet "B" should be YELLOW)
While I could do this with a conditional format in every referenced cell, it would take too long to enter them.
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
ABCDEFGHI need to create a macro to perform some conditional formatting1DateDayTypeTimeConfirmed Order No.Site Location2SHEET OVERVIEW301/01/08TuesdayAB4CVertically there will be 366 tables to represent 366 days and Horizontally 10 tables to represent 10 employees,5Dwhich enables the work activities of 10 employees to assigned over the period of a year.6E7AAFIn the type box marked 'A' and 'AA' the user selects from a drop down box a parameter as list below left.8GOn entry of an 'n' in the type box the user can enter data into the 'time','confirmed order','site location' boxes.9HThe idea of having 2 x type boxes is to allow the day to be split into morning and afternoon.10I1102/01/08WednesdayMACRO / VBA FUNCTIONALLITY REQUIRED 1213In the 'type' boxes if the user selects anything other than 'n' then the 4 'site location' boxes and coloured and 14the appropriate text from the list below left is inserted into the 4 associated 'site location' boxes.1516Example:1718Box marked 'A' the user selects 'h'. Boxes marked 'BCDE' and filled with colour and the text holidayBox marked 'AA' the user selects 't'. Boxes marked 'FGHI' and filled with colour and the text trainingType List Entry ParametersWhen the user selects type 'n' after the associated boxes are returned to there blank state (no colour or text)nnormal dayhholidayThe macro/vba would have to respond on everytime a 'type' box changesssickttrainingThere would be 2 x type boxes per day, 366 days a year and for 10 employees. Therefore it would have to monitoruunauthorised absence2 x 366 x 10 (7320) type boxesbbank holidayccompany shutdown
View 9 Replies View RelatedI've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
To run a macro from the first tab of the workbook but have it do it work on another tab.
In the workbook in question I have a tab called "input" were certain information in updated and is linked or used in formulas on other tabs. Some of the tabs have macros that do various functions. I would like to set up one macro button on the "input" tab that would run the macros on the other tabs instead of having to run each one individually from its own tab.
Below is on of the macros that I would like to run from the "input" tab and have it update the "AP Accrual" tab
Sub CopyPriorMonthAccruals()
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,-1)"
Columns("L:L").Select
Selection.Copy
Columns("Y:Y").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B3").Select
ActiveCell.FormulaR1C1 = "=EOMONTH(Input!R[5]C,0)"................................
I would like to make a macro that loops another macro and changes a value in a cell, like such:
Run macro
if cell A1 = cell A2
Stop
Else set value in cell A1 -0.1
Loop
How exactly would i do that?
I have a little bit:
Sub Macro2()
Call wantedmacro
With Range("A1")
If .Cells(1, 1).Value = "A2" Then Exit Sub
Else Range("A11").Value = (A1-0.1)
Call Macro2
End Sub
I have a macro which loops through data, deleting those rows that are marked with a "Y". However - it appears to always want to do this in three stages. The first time I run the macro, it delete's a large chunk of those marked rows - but it nearly always leaves two rows. The second time I run it, it deletes one of those two remaining rows. And then I run it again and it deletes the final outstanding row. I'm sure i've done something stupid here - the code is below
View 2 Replies View RelatedI need to remove hyperlinks across probably 30-40 workbooks, is there a way of doing creating a macro that would do this across many instead of 1 at a time?
View 9 Replies View RelatedEverytime a cell (say B18) changes value I want the following to happen:
If it changes to "Annual" I want row 20 to ungroup
If it changes to "Quarterly" I want rows 22-25 to ungroup
If it changes to "Monthly" I want rows 27-38 to ungroup
From what I can see on other discussion groups I need to enter a code on the worksheet from view code... but I cant work out the code that I would need to enter.
I have a file with date from the whole year.
Now i want that he splits for me in the file the weeks. So that i have all the info for each week. (seperating by 2 or 3 blank rows)
A second thought was to copy paste them to a new sheet for each seperated week.
and if i just splits them by inserting blanks rows between the weeks. I was wondering if it is possible to make a pivot for each week
i thought maybe working with boxname?
I am sent an email that contains multiple blocks of information.
They're broken down like this:
Title
Link
ID
Department
Date
Status
Value
News
There are about 100 of these blocks of text in the email, and some of them are duplicates. What I'd like to do is copy them into Excel, and run a macro which separates them into the appropriate column and removes the duplicates. The problem that I'm running into is some of the blocks have a value (highlighted in red above) and others don't have anything at all. I'm looking for a solution that will be able to evaluate the text and if the cell after Status isn't value, I'd like it to insert a blank cell, a cell that says ignore, or something that will keep the format correct.
I've been using a Macro that was written in an Excel spreadsheet and it's been working fine. For some reason, the Macro does not work anymore.
The Macro is written in Visual Basic and it is launched with button.
What is the first step I should take to debug this?
I think it's something obvious I'm missing.