ColA-ColB
A1 - B1
A2 - B2
Let's say B1 has a formula- =A1
b2 has formula = =A2
I want to copy all the formula in ColB
As it goes down in Column B, it should be =A2 at 2nd row, =A3 at 3rd row, =A4 at 4th row.
Reason being I want this formula to activate when the cell in Column A is filled. If the cell in Col A is empty, The formula in Col B should be blank!
Example:
ColA-ColB
13/01- Jan
13/12- Dec
29/06- June
30/07- July
I am trying to do can’t really be recorded, at least i don’t think it can. I have a report that I run off and after I loaded it in Excel I then have to add in five columns at the end of the sheet. These are always in the same columns: R,S,T,U,V.
These are the formulas I have to then input
R =IF(G2="no invoices",A2,"")
S=IF(I2="Match",A2,"")
T=IF(I2="Sent to AP",A2,"")
U=IF(I2="Force Settled",A2,"")
V=IF( COUNTIF($R$2:$U$10054,A2),A2,0)
Note for column V, the range R2:U10054 would depend on how many rows is in the report, its just in this example the report does have 10054 rows. The columns would always stay the same. I would then copy the formulae down for the number of rows that is in the report and filter column V by entries that equal zero to give me the rows I am looking to analyse. Is this the type of thing that can be written as a macro so that everytime I have a new report open in Excel I can just run the macro and it will do all of this for me?
I have the following formula:
=AVERAGE('sheet 2'!C7,'sheet 2'!D7:Z7)+AVERAGE('sheet 2'!C9,'sheet 2'!D9:Z9). When I copy it down to the next cell the valules need to increase to 12 & 14 respectively.
What I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg).
Sub ChkDates()
Dim c As Range
Dim DelRng As Range
Dim ArcRng As Range
Dim i As Long
Dim l As Integer
Application. ScreenUpdating = False
Worksheets("Report").Activate
For i = 60 To 8 Step -1
Set c = Cells(i, 33)
If IsDate(c) Then ..............
I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
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.
I need to Fill formulas down the column.
View 3 Replies View RelatedHere is my dilemma, I have two formulas that reference another sheet, my example shows it more clearly...
...............Column E
Row 11......=sheet1!F16+sheet1!I16
Row 12......=sheet1!G16
Row 12......=sheet1!F17+sheet1!I17
Row 12......=sheet1!G17
etc.
Is there a macro or a formula I can use on the remaining 4000 rows that will keep this pattern while maintaining the formulas above?
I want to copy an active cell in row 2 (I dont want to fix a cell as it can be any cell at any time). I want to copy it all the way down to a cell where the next row after it, is completely blank on all the 256 cells.
View 6 Replies View RelatedI wrote below code, I am expecting column to be updated as 1, 2, 1, 2, 1 ,2 till last row, but it is updating as 123456...
[Code] .....
I need it to autofill just as if you selected range A1 and A2 and double clicked that little square on the bottom right hand side of the cell (so it stopps auto-filling at the last active cell in B)
View 5 Replies View RelatedI currently have this code ( which works perfect ) that was written by someone else. It performs an auto-fill in column "A".
I need to learn how to adjust this code to do it for other columns, but do not know currently.
Maybe someone could highlight of BOLD the adjustments to direct the code to the column.
Sub fill_rows_A_4()
Dim arrTmp As Variant
Dim lngRow As Long
With Worksheets("sheet1") 'adapt
arrTmp = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
For lngRow = 1 To UBound(arrTmp)
If arrTmp(lngRow, 1) = "" Then arrTmp(lngRow, 1) = arrTmp(lngRow - 1, 1)
Next
.Range(.Cells(1, 1), .Cells(UBound(arrTmp), 1)) = arrTmp
End With
End Sub
I want to create a spreadsheet table of values that are linked other spreadsheets, a condesnsed version... I am not a total amateur and I understand the world of excel links....
However the problem lies in the fact that the source files change format every day, what i need is a formula for each cell (i know...) that can:
1. Firstly find column and row heading titles by looking for part of the title i.e. for a title like 'Occupation' i need a non case sensitive search for 'Occu'.
2. Once colum and row are found, return the intesecting value.
The formulas are needed so all I will have to do, after this is finished, is update the source files and the relevant numbers will be found automatically.
Im sure this is something that can be done with a clever array formula, setup to recognise column and row intersections based on positve returns on search variables, however this one is just outside my capabilities....
What I would like to do is on a sheet when I insert a new row that it will "FILL" the formulas that are the row above it. For example I have cells A1-F1. On cell A1 there is 1, B1 there is 2...etc. When I then insert a new row I would like the row below A1-F1 to read. A2 = 2, B2=3 so it had a linear growth. I want to do this with my formulas so whenever someone adds a new line it knows to copy the formula as well but only in certain cells if possible.
View 9 Replies View RelatedI am fighting with this for a longer time now and can't find a solution:
is it possible in Excel to generate a number sequence (to create a list for auto fill) representing numbers like this:
000001
000006
000002
000007
000003
000008
000004
000009
000005
000010
000011
000016
000012
000017
000013
000018
000014
000019
000015
000020
and so on till 001000
Working with 2 worksheets in the same spreadsheet. In worksheet A cell F6 is a solution (hex2dec) these solutions are staggered consistently in column F (F6, F9, F12, F15 etc) In worksheet B cell A2 displays workshop A cell F6 details (=ATR!F6)
Worksheet B cell A3 Autofil uses (=ATR!F7) I want (=ATR!F9) Worksheet B cell A4 I want (=ATR!F12) How do I condition autofil to follow my chosen sequence ie F6,F9,F12,F15 etc...
when I fill the information into column B on pages 1-31 I would like it to refer to the location names from the loc-mileage sheet and fill in the suggested name as i'm typing.
I thought about doing a drop down but the list is too long and I dont want it to be set to only use the list names, only suggest names from the list.
very specific Auto-Fill?
FIBER 001
FIBER 001
FIBER 002
FIBER 002
FIBER 003
FIBER 003
Is it possible to auto fill this having a duplicate cell for each number? If so, HOW?
I want to have colums A2 to A32 filled with dates automatically on selecting month from A1 dropwown(validation list).. how can that be done
View 2 Replies View RelatedI need to auto fill quarters but Excel fills years instead. For example I write Q1-11 in cell A1 and when dragging down it fills with Q1-12 rather than Q2-11.
View 5 Replies View RelatedI have this script to auto fill down but something isn't right can someone point out what is wrong?
View 3 Replies View RelatedAuto fill downwards until row value change
View 3 Replies View RelatedI am trying to copy a selected cell but instead of just a copy past, I want to auto fill where it will increment the number.
ActiveCell.Offset(-1, 1).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
' Selection.AutoFill Destination:=Range(ActiveCell.Offset(0, 0)), Type:=xlFillDefault
' Selection.AutoFill Destination:=ActiveCell.Resize(ActiveCell.Offset(0, -1).Value, 1), Type:=xlFillDefault
Application.CutCopyMode = False
I have tried the above code but it hangs.
I am trying to figure out how to auto-fill a range. I am summing a range from one tab to another, like A1:A5. I want to autofill the subsequent cells to begin with the cell that follows the last of the previous range, so it would autofill as A1:A5, A6:10, A11:15, etc. how to do this?
View 3 Replies View RelatedI have a macro set up to automatically fill all of the cells in certain columns with the top cell's formula in that column. This is so I don't have to go through and manually "fill down" the formulas into the blank cells after I insert a row into my database. The problems is, though, that the macro sometimes takes a long a** time to run (and uses up a TON of memory...the doc. was 29,000 kb at one point!) because of the amount of rows & columns I have (approx. 5000 row & 34 columns) just one one sheet.
Anyway, is there a way to set up something in VBA to have it auto fill in the formulas in the columns I choose, after I insert a row, with the information that preceeds it?
So, for example, if I insert a row anywhere in my database, excel will know right away that I want that row filled w/ the formulas from the row above it.
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 RelatedI need to be able to click on a box and have it fil that box with a check, X, or other fill and have that fill go away if I click on that box again. Can I do that?
View 9 Replies View RelatedI use this code to start at the the top of the column going down, but the way my data is setup, I need it to start at the bottom and work up.
Sub fillBlanksI()
'Declare variables
Dim FR As Long, LR As Long, i As Long, FillVal As Variant
'Stop screen flicker and speed up code
Application.ScreenUpdating = False
'Find the last row of any column in the sheet
LR = ActiveSheet.UsedRange.Rows.Count
'Loop from row i downwards until we find a value - this is the first filled row and assign its value to FillVal
my macro simply activates a cell and all i want it to do is to auto fill downwards!
its intended to autofill down for about 100 rows but just cant figure it out!
but keep in mind the cell is always going to change so it cant be fixed
i.e this wont work:
Selection.AutoFill Destination:=Range("C95:C659")
Range("C95:C659").Select
ActiveWindow.SmallScroll Down:=513
because i need it to be autofilled from the CURRENT active cell only!
i have just finished filling out a 2000 line spreadsheet, on this spread sheet i had a customer number i then had to look up the customer number on our old database to retrieve the address for the customer. Is there any way that for future spreadsheet's i can input the customer number and it will auto fill the address of that customer ?
the columns on my sheet are:
customer A/C number
Customer Name
Customer Address line 1
Customer Address line 2
Customer Address line 3
Postcode
it would save me hours and hours of time digging through the database again.