Auto Fill Down Formula From Active Cell
Mar 11, 2013
I am desperately find a way to fill down formula from active cell, example given below,
AA
BB
CC
Total
QTY1
QTY2
City 1
5
2
3
10
12
15
[Code] .......
I need to fill down col Qty 1 & Qty 2. Number of Rows and Columns vary in my Work Sheet.
My code below, Ctrl+Enter not works.
HTML Code:
Cells.Find(What:="QTY1").Activate
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-3]"
'Fill Down Active Column
Cells.Find(What:="QTY2").ActivateActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=+RC[-5]*RC[-3]"
'Fill Down Active Column
End Sub
View 3 Replies
ADVERTISEMENT
Apr 9, 2014
I need a simple macro to use for conditional autofill of cells below the active cell (to the end of the sheet) with the text that is being entered in the active cell.
DESCRIPTION: I have two columns of data, the first (call it Col1), has a word number which is unique to each specific word in a foreign language, every cell in this column has a number in it from 1 to 30000. Each number reoccurs many times in cells over the length of this column.
The other column (call it Col2) is blank. As I type words in english in each cell of this column, I need the macro to autofill the text that I have just entered, into every cell in Col2 where it's Col1 number is a match with the Col1 number where I am typing.
For example, in one row, Col1 has the number "21" and I type in Col2 the word "run". I need the macro to find every row where the number 21 is found in Col1 and automatically enter "run" into Col2 at that location.
The macro must only autofill cells which have no data in them. Preferably from that cell down, although if that is not possible, all cells would work seeing we are only replacing blank cells with data.
View 5 Replies
View Related
Jan 6, 2007
what is prompting this formula to auto fill down when a value in an adjacent cell is entered.
In column A I enter an unformatted fax number.
In column B I use the following Text Formula:
"="("&LEFT(A20,3)&")"&MID(A20,4,3)&"-"&RIGHT(A20,4)
Someone in my office copied the workbook and now any time a value is
entered into the next available Column A cell. The cell in column B will automatically apply the above formula and format the fax number. The person who copied the workbook does not know how this is working nor does anyone else in the office.
There are no macros embedded in this workbook so no event is firing to cause this.
I have attached a small copy of the workbook called "Auto format1.xls"
How to use:
-Select cell B20 and not nothing is in it.
-Enter any 9 digits in A20 and B20 somehow
copies down the formula from B19.
how to duplicate this.
View 9 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
Dec 19, 2013
Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
Attached File : samplev1.xlsx
View 1 Replies
View Related
Jan 24, 2008
I created a database and made one small mistake doing it. The database is infinite amount of rows down and my formula needs to be corrected as such. In the current cells: (C1:C1000) i have the formulas referencing cells $B1, but i need it to reference $B$1. In C2 I need it to reference $B$2, C3 $B$3 and so on. If I autofill, it obviously just keeps the following cells at $B$1 and i can't keep the dollar bill sign.
View 9 Replies
View Related
Jun 14, 2013
I have an excel spreadsheet like the one attached. My problem is column A has a ton of blank cells. Wht I'm trying to do in Column A is write a formula that fills in the blank cells with the number of the last previous filled in cell. For example the first number is .25 I want to fill in the blank spaces below it with .25 all the way until it reaches a different number which in this case is .219.
Once it reaches .219 I want it then to fill in the blank spaces below it with .219 until it reaches a different number. So basically I'm looking for a formula to fill this in on its own instead of having to drag the cells over and over again manually.
In the excel spreadsheet attached I have in Column D the end result I wish to accomplish.
example.xlsx
View 5 Replies
View Related
Nov 1, 2007
I would like to auto fill the present date if another cell of the same row is typed in and then lock that cell with the date.
For example. If someone types in cell A2 then I would like to auto fill D2 with the date 11/01/2007 and lock it.
If someone tomorrow types in cell A3 then the date would once again be autofilled in D3 to 11/02/2007 while D2 would still display 11/01/2007.
View 9 Replies
View Related
Feb 6, 2009
I'm trying to use the coding below to insert a formula down the lenght of the report but it is coming up with a run time error.
View 2 Replies
View Related
Jul 4, 2012
I have a workbook that has a "summary sheet" and then forty some sheets thereafter. Each row on the summary sheet represents a sheet in the workbook. For instance, i.e. on the worksheet tabs below it is displayed left to right as follows: "summary page"(sheet1), "aaa"(sheet2), "bbb"(sheet3), "ccc"(sheet4) etc. On the summary page, row one identifies the aggregate of sheets in a horizontal fashion, i.e. cell A1: "aaa"; cell A2: "bbb" and cell A3: "ccc" etc. I am pulling data for various parameters as columns on the summary page relative to each row (representing each sheet) via "SUMPRODUCT" and "COUNTIF" formulas. The formulas are a constant as each sheet's rows and columns are identical, the only variable in a given formula is the sheet name.
For example: =COUNTIF('aaa'!$C$4:$C$16,"Online")
This works fine if I drag and auto fill the column on the summary sheet, except for the fact that I have to manually type in: 'bbb' in the formula for the next row down in said respective column (COUNTIF('bbb'!$C$4:$C$16,"Online") and 'ccc' (COUNTIF('ccc'!$C$4:$C$16,"Online")and so forth forty some times thereafter.
Given that I am encountering this issue over multiple columns, I am easily going to have to manually alter some 300-400 cells at this rate should I not find a solution. I know there must be an easier way, I have tried creating a 'Custom Autofill List' but this does not translate to the formula bar and thusly will not fill the series. Additionally, I have tried to create a Macro but my lack of knowledge in VB and overall in this area of Excel has proved to be a difficult task..
View 3 Replies
View Related
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
Dec 8, 2006
I am aware of the ColorIndex, but more specifically I need to change the fill color of a cell that is 9 columns to the right of the active cell. I can't find out how to make this work. to clarify:
ActiveCell + 9 columns.Interior.ColorIndex = Yellow
There has got to be an easy way to do this.
View 3 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
Jun 22, 2006
I am attempting to write an Excel Macro that would allow me to Click on a particular row and have the fields in my form fill with the data in the columns of the row.
I am fairly new to Macros and VBA, but know that this has got to be a possibility.
View 9 Replies
View Related
Mar 23, 2009
I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE
Sub FORMAT_AS_A_TABLE()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5"
End Sub
View 3 Replies
View Related
Jun 25, 2006
What I am trying to do is to give the user the ability to insert a photo and have it autosize into a range of cells--so far I can only get it to work with a single cell and a predefined file. Can this be refined so that rng references a group of cells and pic somehow lets the user input a file name or pick its name and location?
Sub test()
On Error Resume Next
Set pic = ActiveSheet.Pictures.Insert("C:
ange.gif")
On Error Goto 0
If Not pic Is Nothing Then 'Found it!'
Set rng = ActiveCell
With pic
.Height = rng.Height
.Width = rng.Width
.Left = rng.Left
.Top = rng.Top
End With
End If
End Sub
View 3 Replies
View Related
Nov 23, 2009
i need to get some information (price) automatically put into another cell when an item from a drop down list is selected, i have attached a workbook to show what i need to do,,,basically if i choose an item from a list in cell a1,,i want cell b1 to show the price of the item,,,and the item is referenced from sheet 3 as well as the price.
View 2 Replies
View Related
May 30, 2008
I have a spreadsheet that is downloaded from an accounting system and requires extensive editing to be normalized before it is combined with data in another database. To normalize the data in a simple manner, I use the following macro:
Sub All()
Rows("1:37").Select
Selection.Delete Shift:=xlUp
Sheets("DL").Select
Columns("A:D").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Check"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Benefitor"........................
It works great, however I would like to not limit my cell ranges for the auto fills. I would like to auto fill to the end of the data, which changes with every download (additional rows).
View 9 Replies
View Related
Feb 17, 2007
When I enter a number into a cell, and then drag down in the column to auto fill the cells below, the number increases. I want to be able to drag without the number increasing in increment.
View 13 Replies
View Related
Nov 14, 2008
Need formula for populate a cell with a date. I have a workbook with 5 sheets (mon, tues, wed, thur, fri) and I'd like to type a date on mondays sheet so all the other sheets follow on the date ie:
Noverber 1st 2008 is typed into sheet 1: A1, sheet 2 automatically displays November 2nd 2008, sheet 3 displays November 3rd, etc..
I know it's basic progression, but I'm not sure how to do it with date/text...
View 2 Replies
View Related
Apr 23, 2009
I have written in some conditional formatting to have a cells automatically updated based on a response in a cell above. That is: If a no response is chosen to a certain question, then all other questions in that range are to also be a No response. I have attached the document im working on with an outline of what the problem is!!!!
View 4 Replies
View Related
Dec 29, 2009
How do you make the auto-fill to not automatically use the next numbered cell:
For example:
I want it to fill with every other cell from a different sheet (same column "D") so i would have a cell ='sheet1'$D10 or whatever then how would i do it so it will auto fill every other cell from then on so the first cell below the initial one would copy from the other sheet as D12 instead of D11?
View 9 Replies
View Related
Jul 2, 2014
When I type numbers in the cells from D4 to D14, E4 to E14 and so on, the particular cell should turn into "Green". All the the cells will remain "yellow" and blank until numbers are entered based on the daily cleaning of machines. When particular machine no is entered in to a cell cell, that cell should turn green(this shows that "service is done for that machine")
View 2 Replies
View Related
Mar 19, 2013
A have three columns with "Kilograms range" and corresponding value.
A========== B========== C
KG From====-To======= Predefined Value
what formula will fill up the cell with the predefined value automatically based on the range, after a kilograms are entered in a different cell as explained in the attachment.
View 2 Replies
View Related
Apr 28, 2009
Is there anyone who can suggest a solution to my problem below ?
Basically, what I need is a macro which would ideally work like this:
if D32 = 1, then the background colour of D4 should be red
if D32 = 2, then the background colour of D4 should be orange
if D32 = 3, then the background colour of D4 should be yellow
if D32 = 4, then the background colour of D4 should be green
And then I plan to use it for columns E, F, G
View 9 Replies
View Related
Nov 19, 2012
I have a userform [AddEditMove] with a bunch of textboxes (something like 30) that create entries in a spreadsheet ["Move Records"] along one row. In addition to creating those basic entries, the textboxes also fill in other spreadsheets after manipulating the entered data in various ways.
Therefore, if an entry needs to be changed, it needs to be changed from the userform rather than just on the main sheet, or those other actions won't be taken. At least, that's how I see it now. I'm interested in knowing how other people have handled similar set ups.
Right now, what I think I'd like is for the user to be able to double click any entry on the main sheet and have that action call up the userform and autofill the data from the sheet so the user can change what they need to change and then update it. It would also require a tweak in the userform code -- "if called from cell click, then fill info in selected row, rather than next empty line" -- if that's possible.
View 1 Replies
View Related
Apr 30, 2014
Is there anyway to automatically fill the empty added cell after inserting a row without using the fill handle? For example, for a series of numbers: [URL]
2. Drag the fill handle Selected cell with fill handle across the range that you want to fill.
Or running balance: [URL]
2. Extend the running balance formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle.
View 6 Replies
View Related
Sep 6, 2007
I would like to auto populate a cell (A2) due to the input results from another (A1).
For example, if I enter A1 with "100%" then I want A2 to auto populate with "Complete". And just the same for other percentages; if A1 was input with any range from 1% to 99% it would populate A2 with "In Progress". And if A1 was to equal 0% then A2 would be "Not Started".
View 10 Replies
View Related
Feb 22, 2014
I have over 20 Userform which have one textbox with the same name txtsejobcode1.
Additional I have a UserForm in which user can search for a needed job code.
I wanted that after a user will choose a job code the job code will be added in to the active userform with the textbox named txtsejobcode1.
I could build this:
[Code] ..........
But in the line with Combobox I'm getting a error 438
Run Time error 438 Object doesn't support this property or method.
View 8 Replies
View Related
May 1, 2009
I've got this macro, that will auto fill in the "username" (Col. J),and the date (Col. K), When a value is "typed" into the column I for the same row.
If I "Copy N Paste" the same value down column I for any given number of rows, the data will not autofill in to (Col.J&K). This only happens when a
value is typed.
Is there a way to fix this so it will auto fill when, a value is "Copied and Pasted"?
Also, how would I set the range for this to happen for row 9 and down.
I don't want the auto fill happening in rows 1-8.
I've attached an example layout.
Here's the code I'm working with:
View 4 Replies
View Related
