Make Macro Work In Many Rows
Jan 31, 2007
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............................
View 4 Replies
ADVERTISEMENT
Jul 1, 2014
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 Related
Sep 13, 2013
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].....
View 9 Replies
View Related
May 7, 2008
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 Related
Aug 21, 2009
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?
View 13 Replies
View Related
Aug 14, 2008
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.
View 9 Replies
View Related
Feb 13, 2010
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 Related
Jul 2, 2008
The 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.
View 9 Replies
View Related
Nov 18, 2013
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?
View 1 Replies
View Related
Jan 10, 2010
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 Related
Feb 7, 2014
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?
View 3 Replies
View Related
Mar 9, 2014
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
View 3 Replies
View Related
Mar 26, 2014
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?..
View 1 Replies
View Related
Aug 24, 2009
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
i have attached the code in notepad ...
View 8 Replies
View Related
May 19, 2014
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,
View 9 Replies
View Related
Aug 11, 2014
I have some problem with my excel formula here. It works in some rows but doesn't in others.
I have attached the excel file herewith : Book1.xlsx
View 4 Replies
View Related
Dec 15, 2008
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!
View 12 Replies
View Related
Mar 6, 2013
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.
View 2 Replies
View Related
Aug 21, 2014
Goal: Hide rows based on value in Drop Down Box
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.
[Code] .....
View 6 Replies
View Related
Jan 21, 2010
I import a sheet and some records have 2 rows some 3. is there a way to add the extra row as needed based on column A?
View 9 Replies
View Related
Jan 6, 2009
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.
View 9 Replies
View Related
Nov 7, 2008
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
View 9 Replies
View Related
Dec 31, 2009
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
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….
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!
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?
View 3 Replies
View Related
Feb 4, 2009
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)"................................
View 9 Replies
View Related
May 19, 2014
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...
View 2 Replies
View Related
Apr 30, 2012
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
[Code].....
View 3 Replies
View Related
Aug 14, 2007
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.
View 9 Replies
View Related
Mar 18, 2012
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
View 4 Replies
View Related
May 28, 2009
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 Related
Apr 17, 2007
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?
View 9 Replies
View Related
