I'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'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??
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.
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.
OR 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:
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.
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?
I'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?..
i 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
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,
I'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 a spreadsheet that only contains 1500 rows but excel acts like it is is using the full number of rows available. I have a print area defined and I have tried deleting the unwanted rows but nothing seems to work.
Problem: The code works fine if i manually change the $D$10. If I make changes to the Drop Down Box the cell link updates, but the rows are not hidden.
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.
I have made a very basic spredsheet which using an assumption table makes a particular column of values which works out the revenue. Then I just drag the box down to the number of rows I want it to go down.
However, I want to be able to enter a number in a CELL away from the tale eg. 50 and have the number of rows in the column go down to 50
Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….
However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?
To put this formula… =IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1) Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?
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
I assume I would need a macro, but I am wanting to have a button where-by the user clicks to create another 100 rows and in-turn, continue the formula's that reside within the columns and rows above, down...
I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.
I have put this code on the features segment tab but it is not working.
Code: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "B40" Then
I have created code to enter data from the Active cell. However, I would like to edit it so that the user can only make this work if they are on Rows 6 through Row 29.
Here is the Sub DropDown7_Change() 'Right here I want: If ActiveCell. is in Row 6-29 Then Application.ActiveCell.Value = Range("D3").Value Application.ActiveCell.Offset(, 1).Value = Range("F3").Value Else Msg = "Move into the proper rows" End Sub How can I do a check before the code is executed to be sure the users active cell is in Rows 6-29.
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
I 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?
