Copy/Fill Down Formula Macro Code
Jun 5, 2008
I have created a macro with the below formula in it:
Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L234")
Range("L2:L234").Select
I am using column K and it looks up the length of this cell. When I created the formula I was using info that only went to line 234 which was fine. Once I clear the info in column K and input new info it can go further than line 234. How do I get the macro to look up the whole of column k without having to create a new macro each time?
View 6 Replies
ADVERTISEMENT
Mar 13, 2008
I want a Macro to be able to copy a formula down from cell A2 down the entire column within the data range. But, the problem I've got is that the spreadsheets I'll be using the Macro on will be different sizes. Currently, the VBA looks like this:
ActiveCell.FormulaR1C1 = "= LOWER(RC[3])"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A17340"), Type:=xlFillDefault
Range("A2").Select
So the "Destination:=Range("A2:A17340")" part copies the formula into all cells from A2 to A17340. Other spreadsheets might only have data up to cell A200, or Awhatever - is there a way of getting the macro to populate just the cells in column A but only where there is data adjacent in other columns?
Also, I want the Macro to be able to delete all rows wherever "DELETE" appears in a certain column - I had a look at the "Delete Entire Row Based on Criteria" Macro but I'm looking for a fully automated solution, rather than the question boxes coming up and asking which cell/criteria, I need to run this macro on multiple sheets and the criteria/column position will always be the same -
View 7 Replies
View Related
May 23, 2014
When I enter data in the cells A1 and B1, C1 needs to calculate the result. (not copy and paste)
For examle: I have formula C1=(A1+B1)/2
C column for formula, but shows result only when there is a data in A and B
View 7 Replies
View Related
Oct 11, 2006
i have a userform named: usf_History.
i would like to refresh all the text boxes (they either refer to cells or are calculations)...?
View 6 Replies
View Related
Nov 17, 2012
Why this error is coming up on a macro. Now to what I'm trying to accomplish is I have 1 sheet called GDL and I want to pull certain data from that sheet into another sheet called data sheet. I have formulas set in the data sheet to pull the information I want. However, I want to create a macro to pull the data for those formula or I guess another way of looking at it would be a auto fill function, except with a macro button. Please see the following error I receive when trying to run the macro.
Run-time error '1004': Autofill method of range class failed. The following is the macro.
VB:
Sub ResetDataSheet()
'
' ResetDataSheet Macro
' Macro recorded 11/13/2012 by asdf
'
[Code] ....
View 5 Replies
View Related
Apr 29, 2008
I'm working on some macros to fill and file paperwork from an IBM Reflection based system into an Excel spreadsheet. Here's the code I've written so far:
Private Sub cmdSend_Click()
Dim ExcelObj As Object
Dim ExcelWasNotRunning As Boolean
On Error Resume Next
Set ExcelObj = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
DetectExcel
If optObligation = True Then
Set ExcelObj = GetObject("S:PublicVisual Basic FormsExcel FormsObligation.xls")
Else..............
The problem I'm running into is that the data isn't loading the first time through. The spreadsheet opens, nothing happens, Excel closes. If I attempt to run the macro again without closing the form and clearing all the variables it runs perfectly as many times as I want to run it (which isn't terribly useful, because I'm stuck putting the same data into the form over and over again). If I unload the form then I get the same problem: first run, no data transfered.
View 6 Replies
View Related
Aug 22, 2012
I am using this code and it works fine:
Dim MyData As Range
Dim MyResult As Range
Set MyData = Range("E1:E1000000")
Set MyData2 = Range("F1:F1000000")
Set MyResult = Range("J4")
MyResult.Select
Selection.Formula = "=COUNTIFS(" & MyData.Address & ",""=Kim""," & MyData2.Address & ",""=done"" )"
Is there any way I can change the code so that it automatically finds the last cell as Im using Range("E1:E1000000") and Range("F1:F1000000") because there will not be more than 1000000 entries.
View 2 Replies
View Related
Oct 11, 2011
I need a macro script that goes to a particular cell in a column based on a criteria (say, locate the first instance where $0.21 appears in col. H), insert a row above it, and fill the row with the text "Note:These are above $0.20"
View 2 Replies
View Related
Feb 6, 2007
I have a formula that is averaging seven (one week) consecutive cells, I want to Copy/Fill the formula down the worksheet for the entire year. Is there a way to use the fill handle to drag down and make the formulas use the next seven colums?
I have removed the $ from the row numbers in the original formula, but when I use the fill handle it fills like this:
=average(B2:B8)
=average(B3:B9)
I would like it to fill like this:
=average(B2:B8)
=average(B9:B15)
View 9 Replies
View Related
Dec 6, 2009
I have some formulas that combine template text and values from cells in that row to result in text that I then Copy and use in another place. I am trying to convert that to a Macro.
My Skills are Novice -- I have searched Google and Tutorials and it seems that my usage is not common so, I have not found answers to help me move ahead.
I'm working with a simplified version of what I want to do to eastablish a
Working model that I can modify and expand. I had a macro that ran to completion but I abandoned it. I realized that I was on a Wrong Track. It was created from recorded macro sections and googled routines and very clumsy.
Heres my Scenario
If my Excel Cells were
Cell A1 = 135252
Cell B1 = $13.95
Cell C1 = 3
Cell D1 = Oversize
I want to run a Macro from E1 that would Result in text of
Our Stock Number is 135252 - The Price is $13.95.
We have 3 in stock. Type of Packaging is Oversize
being left in Cell E1.
I would like the Macro be able to run on other rows as well when launched from Cell E of that row.
My Goal would be to make the macro work on a range of rows by Highlighing Cell E in those rows.
My Excel Formula to accomplish the same thing is -
="Our Stock Number is "&A1&" - The Price is "&B1&".
We have "&C1&" in stock. Type of Packaging is "&D1&"."
If I could get this much working, I think I can expand and edit to encompass my actual application.
View 9 Replies
View Related
May 6, 2014
I'm using 3 sheets within one Excel file. 1st sheet (target) is called Offer, the 2nd one (source) is called Stock and there's a 3rd one that only contains a button to which i'll assign the final macro. Now, back to it.
I need to copy from Stock to Offer contents of the entire column (basically there are a few more in Offer, so i need to copy each one and place it in a different part). I need to copy from ROW 2 and paste in ROW 6. The length of the document varies, as it's connected to the database (now i can have 3800 rows, in the next hour 3765). Previously i just added an X to row 4500 ( i don't expect to EVER have more than 4000 lines) and just copied from 2 to 4499
Second issue is, on the Offer sheet i have to fill a column with a certain value. Again, it needs to start from Row 6, and needs to be as long as the adjacent column. To get a better idea, i have for example 3800 price lines, so i need the column next to it to be filled with 3800 rows saying GBP (this will be a fixed value).
Also, none of the codes above should exclude blank cells in the middle of the string. Last thing, seems some of the barcodes i have only have 12 digits, so i'll need to add a 0 in front of all shorter numbers to go up to 13 digits.
[Code] ......
I should be able to figure the rest by recording bits and pieces. The Save part is done already, i'm using
[Code] ....
I needed it in XLSX format, not XLSM so that suits just fine. I might need to add hour and minute as well but i can sort it out (as i said, i can do some stuff on my own but this one got me to the bottom).
View 2 Replies
View Related
May 27, 2008
In a worksheet I retrieve data from a SQL query. Now I have to add 5 different calculations per row and each calculation will be stored in a new cell (so 5 columns will be created)
I want to do this with a loop macro and calculate this till the last "filled" row. The number of rows differs every month.
What loop code should I use and can somebody give me an example of the VBA code.
Note that some formulas contains nested If-functions.
View 6 Replies
View Related
Dec 10, 2012
As seen from my attached file,
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
FormattingHelpExample.xls
View 2 Replies
View Related
Mar 2, 2014
b2 = a2 * 150
c2 = a2 * 145
d2 = a2 * 140
e2 = a2 * 135
[Code].....
in the first row i want to use the fill handle at 135 and drag rightwards so as to copy/retain the pattern (decrementing by 5, from multiplier 150 until 100)
how would I achieve that?
p.s I also want the formula for columns to work with the fill handle drag downwards.
View 1 Replies
View Related
Jan 8, 2009
I am looking for a Macro to fill in a formula in each row where there is a value. So if there is a value in row 1 Column A then place this formula into Column B and continue down the sheet untill there is not a value in Column A.
View 9 Replies
View Related
May 21, 2014
I need a macro that will insert this formula: =Trim(IF(B3="","",MID(B3&", "&B3,FIND(" ",B3)+1,LEN(B3)+1))) into column C starting on row three through the end of the spreadsheet information.
I typed this incorrectly when I first posted. I need it to fill down into only column C not the end of the spreadsheet.
View 14 Replies
View Related
Jun 6, 2006
Let's say I have data from column A up to column J.
Columns M, N, O contain formulas which calculate value from column J. Since this sheet is a template, the number of rows of data vary from month to month, i.e. the amount of records I copy and paste to columns A-J vary.
I would like to have a macro which will fill down the formulas in columns M, N and O as many as there is data in column J. Example: Let's say minimum number of row is 100, so up to row 100, column J already contains formulas. But if the following month the number of rows is 150, I would like the macro to automatically fill down the formulas in columns M, N and O up to row 150. But if the following month, there is only 130 rows, the macro should fill down to row 130 only.
View 4 Replies
View Related
Jun 12, 2008
I have written a macro that creates a new spreadsheet and copies text into it. The number of rows varies depending on the input.
Yet, running the macro results in a sheet with the max rows (65k+). This is unmangeable, as it makes it a huge file and size and harder to print.
I only want there to be the number of rows that have text in them.
The larger problem is how these were even created in the first place. Currently:
' Formula for Job Name
Range("A2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[12],8)"
' Fill Job Name
Range("A2").Select
Selection.AutoFill Destination:=Range("A2", Cells(Rows.Count, "A")), Type:=xlFillDefault
View 3 Replies
View Related
Nov 21, 2007
I want my column to be populated by formula using VBA. Let's say on row1, the formula is = SUMIF(mysheet!$L:$L,$A1&C$1&"09",mysheet!$M:$M). And on row 2, the formula is
=SUMIF(mysheet!$L:$L,$A2&C$1&"09",mysheet!$M:$M). And on row 3, the formula is =SUMIF(mysheet!$L:$L,$A3&C$1&"09",mysheet!$M:$M). And this is done up to the last row. How do I do this?
View 9 Replies
View Related
Jul 17, 2014
I've created the following Macro based on some of the posts on this great forum! It works great, but I'd like to tweak it for another purpose where it would fill the formula down until a cell in column AF had the value "#N/A" or stopped having the value "clean". The #N/A is from a formula result so I'm not sure if or how the Macro would treat this.
Code:
Sub Y_CleanUp3()
'
' Clean Up Bad Data Macro 3
Dim LR As Integer
Range("AH2").Formula = "=VLOOKUP(X2,'[Territory by Zip Code.xlsx]Sheet1'!$A$2:$B$135000,2,TRUE)"
LR = Range("AF" & Rows.Count).End(xlUp).Row
Range("AH2").AutoFill Destination:=Range("AH2:AH" & LR), Type:=xlFillDefault
With Range("AH2:AH" & LR)
End With
End Sub
View 2 Replies
View Related
Sep 27, 2011
Trying to use a standard formula based on a variable workbook name. The INDIRECT solution suggested worked fine until I realised that I'd need to have the additional workbooks open in order to return the results.
Variable Workbook name in formula?
The reason I wanted to use a standard formula was so that I could use a piece of code I've written that would simply copy the formula from one cell and paste it accross and down for all cells within a range of rows and columns. Seeing as the formula now needs to be unique per column I need to change the code to copy the entire row of formula down instead of the one formula down and across. Here's my original code that copies a formula from B2 and pastes it down to the last row and across to the last column:
Code:
Sub CopyFormulaDown()
Dim lngLastrow As Long
Dim rngTargetStart As Range
Dim rngTargetEnd As Range
lngLastrow = Sheet3.Range("A65526").End(xlUp).Cells(1, 1).Row
[Code] .....
What I assume I need is to change the Range("B2") in the last line to Range("B2:???"), where ??? is the last column eg AA2. I'm not sure how to do this dynamically though.
View 2 Replies
View Related
Oct 31, 2011
But I can't get the array formula to copy to the LAST ROW
Sheets("LEDGER PREP SHEET").Select
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F1").Select
ActiveCell.FormulaR1C1 = "Name ID"
Range("F2").Select
[Code] ........
View 2 Replies
View Related
Apr 25, 2006
I need some come code for a macro to copy a formula down a set of cells (I want to highlight a set of cells then run macro). I've looked all over the web, and the ones that looked right didn't work. I'm afraid I don't have enough experience yet to edit them.
In detail: Have cells b2 down list the price of an item
Have C down list item price plus VAT
Create a formula in C2 (ie =(b2/100)*117.5
Copy C2 to all rows with a value > 0
Create a macro where I highlight cells to apply this macro to
This is all in aid of an interview tomorrow, so if I'm lucky, I can spend more time afterwards learning this stuff. I get the principles, just don't know the language enough yet.
View 2 Replies
View Related
Jan 2, 2012
I need multiple macros to copy information from sheet1 to sheet2 and another to copy the information from sheet1 to sheet3 and so on. need a total of 20 macros each to copy information from sheet1 and paste it to the corresponding sheet number. How to do it and this this is what i want to do, Copy roll A-B-C-D,and G but not E-F-H i have formulas that i don't want to clear or re-do every time it is possible to do this.
a B C D E F G H
HarvestDate VarietyL/P Code Label Pakagin Qty Total
1 12/2/2012 Soda 23 3
1 12/12/2012 plants 24 34
1 12/13/2012 socks 25 22
1 12/14/2012 shoes 26 22
1 12/15/2012 tie 27 22
1 12/16/2012 cds 28 22
1 12/17/2012 computer 29 22
1 12/18/2012 tapes 30 22
View 8 Replies
View Related
May 1, 2008
I have 2 workbooks that I am working out of. Workbook number 1 is where the data is going to be saved from workbook number 2. I need to copy data from workbook number 2 but there are several road blocks. 1) I need to search for specific information in column E. Anytime information is listed under this colomun it is listed 2 times one on top of the other with a different peice of information to the right of each one. note that the items that I am looking for on this sheet will no always be there.
What to copy under E / F
E F
1 BAR | $5,123
2 BAR | 24
Once the first "Bar" has been found, F1 & F2 need to be copied to workbook 1 to different locations on the active sheet. The names will always be listed together and the data will always be to the right.
View 2 Replies
View Related
Feb 18, 2008
I have a macro that inserts a formula on the first row and then copies it down. The problem is the row count is variable but I know it won't exceed 5000 so I just copy it down 5000. For the blank rows I get the ugly #NAME, #VALUE, etc errors.
I know there are ways to supress those error messages, but how about a way so that the macro checks to see how many rows are populated so it doesn't copy down on the blank rows.
View 9 Replies
View Related
Dec 30, 2011
Here is the code I have:
Windows("2.xlsm").Activate
Sheets("Report_P").Select
Sheets("Report_P").Copy After:=Workbooks("New_report.xlsx").Sheets(9)
2.xlsm is open. it contains a sheet called Report_P New_report.xlsx is open and has 9 existing sheets
Every time this tries to execute I get a "Run time error 1004 Copy Method of Worksheet Failed".
What is wrong with this code? I have an identical line in another macro, the only difference is that there are 5 sheets pre-existing, and that works fine.
View 6 Replies
View Related
Sep 8, 2006
I have to issue template workbooks to people for budgeting purposes.
Within the workbooks are various numbers of worksheets pre -formatted and ready for these people to enter data.
Some of the data in the worksheets is important to them as individuals but not to me so I have a series of macros that lift the information from the worksheets and put it into a worksheet more specific to me.
Because the users are on the whole not that good with Excel I have put in easy to use look up tables and various proctections to stop them adding or deleting rows or columns as this plays havoc with my macros.
There is one thing I have "so far" been unable to do and I wondered if anyone could help.
Is there a macro that I could put somewhere in the workbook which would detect when someone tried to cut and paste and would either put up a message box or stop them doing this?
The reason being they are cutting from one row to another and this is messing up the calculations which are protected.
Is there also a way to stop them changing the name on the sheet tab in the smae way?
View 9 Replies
View Related
Apr 17, 2008
I have a long header I am pasting to a column "A" on a new Sheet. When the header pastes it is repeating itself but with long blank spaces in between each repeat. The first time goes from A1 to A152, which is all I want. But it shows up again starting at A180225, and again at A212993, and again and again. I only want a single instance of the header in Column A. Here is the macro I am using:
Sub Sort_Cells()
Rows("1:1").Select
Selection.Copy
Sheets.Add after:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=True
Selection.Columns.AutoFit
Range("B1").Select
End Sub
View 4 Replies
View Related
Jun 8, 2014
I want text that's in bold in column B in sheet 1 of my spreadsheet to copy over into column A in sheet 2 of my spreadsheet - is there a quick way of doing this using code or a formula?
View 3 Replies
View Related
