AutoFill Formula Code
Jan 4, 2008
I am trying to use autofill over a range of cells.
I am using a Do While loop to find an empty column in row 6 incremented by 5 based on LFound. The starting value of LColumn is 9 and is increased based on and IsEmpty condition.
Do While LFound = False
LColumn = 9
If IsEmpty(Cells(6, LColumn).Value) = True Then
LFound = True
Else
LColumn = LColumn + 5
End If
Loop
Next I want use this column index to put an equation in a cell
Cells(15, LColumn).Formula = "='" & SheetName & "'!C103"
Last I want to autofill the 8 cells below the refrenced cell.
Cells(15, LColumn).Select
Selection.AutoFill Destination:=Range(Cells(15, LColumn) & ":" & Cells(23, LColumn)),
Type:=xlFillValues
But I can't get the autofill to work. I think it is because of my attempt at going from Cells() to Range(),
View 3 Replies
ADVERTISEMENT
Oct 30, 2008
I have this bit of code;
Selection.AutoFill Destination:=Range("j2:j20000")
But I want it to only autofill as far as the data goes in column I rather than down to row 20000
View 9 Replies
View Related
Jun 22, 2014
It has been a while since I worked with VB and I am struggling with some simple code.
I have an Excel sheet that I am using to track client information on, the client name starts on A3 and the list of clients we are tracking will vary from week to week.
I have values in B3:D3 that I need to auto fill based on the values that I have in column A. For example, if column A goes to row 33 then I want to auto fill B3:D3 down to row 33.
The code that I have now works as band aid, but I need something more dynamic.
I have found some solutions on line but none of them worked on varying lengths, the following is the code that has worked so far.
Sub Autofill()
'
' Autofill Macro
'
Range("B3:D3").Select
Selection.Autofill Destination:=Range("B3:D36")
Range("B3:D36").Select
Range("B3").Select
End Sub
View 3 Replies
View Related
Aug 7, 2007
Just a niggling problem, I've got lstRow as a Long and it contains the value of the last row offset by (1, 0). The problem is i'm trying to add it into a range
Range("C500:K500").Select
Selection.autofill Destination:=Range(C" & lstRow &":K" & lstRow &")Type:=xlFillDefault
Like that.. Except i've tried a million different combinations of " and & in different places to try and get it to compile. It refuses too. It always gives the error "Expected list seperator or )" I know I could just do it individually from C to K but I'd like to learn how to do it this way as well.
View 6 Replies
View Related
Mar 24, 2014
I'm looking to populate a series of sequential codes based on a code prefix.
Sheet 1: Master Code list
Column A = Lookup Value
Column B = Code Prefix
Sheet 2 - Results page
Column A - list of lookup values - sorted in value order - The number of occurances varies for each value
Column B - Results list
I want to populate Column B in the second sheet with a sequential code list for each different value in column A. So for the first value it would have the prefix then proceed numerically (Prefix0001, Prefix0002, Prefix0003...) then for the next value its prefix in sequence (2ndPrefix0001, 2ndPrefix0002, 2ndPrefix003...), etc
I'm wondering if there is a way to do this all in one step or else if there is a way way to number each occurance of the lookup value in the second page, in which case the code can be concatenated easily by a lookup of the code.
I suppose on the back of this there is another query - is there a way to concatenate a number where the format is 000# where the concatenated value will retain the leading zeros? I've been trying and it is stripping them out.
View 2 Replies
View Related
May 4, 2009
I have the following formula that I would like to be able to autofill.
=UniqueItem('Divide Outs'!$A$2:$A$999,1)
The 1 after the comma needs to count up...anyway to autofill a formula like this?
This is a function that i use to display unique listings.
View 3 Replies
View Related
Mar 29, 2007
I have data in colA. In colB I want to auto fill a formula that is in cell B1 all the way down and stop when the data in A stops.
Range("A1").AutoFill Destination:=Range("B1:B" & Range("B1").End(xlDown).Row)
It doesnt seem to like this though and I think ive got the cols all wrong in the formula.
View 4 Replies
View Related
Jul 18, 2012
I have the macro to insert a formula and copy it in a range("A2:A14", but I want the end of the range in column A to be the last row with data in column B. How to add that in?
Sub vlookup()
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[1],'[Old.xlsx]Total Student Count Data'!R2C2:R1000C2,1,FALSE)"
Range("A2").Select
Selection.autofill Destination:=Range("A2:A14"), Type:=xlFillDefault
Range("A2:A14").Select
End Sub
View 2 Replies
View Related
Aug 1, 2012
The code below inserts a formula into E2, copies it across to column O and then down to the last row. It works fine but if more columns are added obviously it would need to be changed to go up to column P for example.
How can I change this so that the formula will be copied across to the last column automatically and therefore the code will not need to be changed?
Code:
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Select
[Code]....
View 2 Replies
View Related
Mar 8, 2013
I'm trying to autofill "vertically" in a spreadsheet using a formula which loads value from a different sheet.
Lets say I have cell (A1 for example) ='2013'!A39
Now I want to fill A2-A30 in a way that the formula updates to ='2013'!B39, ='2013'!C39, ='2013'!D39...and so on
I've tried using ='2013'!A$39, but this only copies the formula as it is. When I try to autofill cell A2-A30, the formula still is ='2013!A$39
Is this possible to do?
View 1 Replies
View Related
Jul 3, 2009
I have an xls with over 500 rows of data, every day I have to update the contents of some of the cells, Cell A contains the date and is auto filled already to the end of 2009, Cell B shows me the number of days since I began the sheet and is also auto filled already to the end of 2009, Cell C & Cell D I have to manually enter data
Cell E contains this formula =D527-D526
Cell F =C527/B526
Cell G = =IF(C527=0,0,C527-C526)
Cell H resorts to manual entry.
My question is "why do these columns with formulas, (E,F & G) not automatically carry the formula to the next row?" I'm sure that they once did. Is it a setting that I can't find?
This is excel 2007.
View 6 Replies
View Related
Oct 3, 2007
need to autofill collum C with a formula related do collum B. If I do this by hand I just type the formula in C 1, and click on the right-down corner of the cell selection, and the formula goes until the last line (last value on collum B). But I want to create a macro to this function, and the problem is that the files that I will apply the macro have different lengths. I want to modify the macro to be able to run from C1 until the end of the values on collum B. I don't want to freeze the last value.
Here is the macro
Sub Macro3()
ActiveCell.FormulaR1C1 = "=60000/RC[-1]"
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C4819")
Range("C1:C4819").Select
Range("E7").Select
End Sub
In other files C4819 will not be the last value, could be 5345 for example, but its impossible to do this, my macro should do: "autofill collum C with a formula until the last value on collum B"
View 9 Replies
View Related
Dec 9, 2013
I am trying to auto fill this formula down a column but it doesn't keep the C4,D4,E4...ETC to stay constant
I manually did these two correct ones
=SUM(C5*C4+D5*D4+E5*E4+F5*F4+G5*G4+H5*H4+I5*I4+J5*J4+K5*K4+L5*L4+M5*M4+N5*N4
+O5*O4+P5*P4+Q5*Q4+R5*R4+S5*S4+T5*T4+U5*U4+V5*V4+W5*W4+X5*X4+Y5*Y4+Z5*Z4+AA5*AA4)
=SUM(C6*C4+D6*D4+E6*E4+F6*F4+G6*G4+H6*H4+I6*I4+J6*J4+K6*K4+L6*L4+M6*M4+N6*N4
+O6*O4+P6*P4+Q6*Q4+R6*R4+S6*S4+T6*T4+U6*U4+V6*V4+W6*W4+X6*X4+Y6*Y4+Z6*Z4+AA6*AA4)
when I drag it down it incorrectly looks like this:
=SUM(C7*C6+D7*D6+E7*E6+F7*F6+G7*G6+H7*H6+I7*I6+J7*J6+K7*K6+L7*L6+M7*M6+N7*N6
+O7*O6+P7*P6+Q7*Q6+R7*R6+S7*S6+T7*T6+U7*U6+V7*V6+W7*W6+X7*X6+Y7*Y6+Z7*Z6+AA7*AA6)
I want C6,D6,E6 to be C4,D4,E4 ETC
View 3 Replies
View Related
May 3, 2007
I have two formulas which I'm using: =180*(120.5-1)/119.5 and =90*(60.5-1)/59.5
In the first example, I want to replicate the formula across row 1, but with the value '1' incrementing by 1 each time, upto 240.
In the second example I want the '1' value to also increase by 1 for each cell down column B, up to 120. when I try dragging the formula down or across, I am only able to replicate the formula exactly as in the initial cell (ie. the '1' doesn't change). I've tried using the $ symbol, but this doesn't work. I don't fancy doing this manually for a total of 360 cells!
View 5 Replies
View Related
Jun 7, 2007
I have two worksheets in the same workbook. In Sheet1, I've got a whole bunch of stuff that I need listed, like names, addresses, phone numbers, etc. What I want to do is pull the values of only the names, which are listed every 42 spots, and put them into a list on Sheet2. So, for example, in Sheet1, A1 is a name, A43 is a name, A85 is a name, etc. In Sheet2, I want to list only the names. And if there isn't a name yet, I want a blank cell. I've sorta figured out how to do it, but I get a problem with the values not incrementing correctly.
So, example code would be
=IF(ISBLANK(SHEET1!A1),"",A1)
=IF(ISBLANK(SHEET1!A43),"",A43)
etc.
The problem is when I drag the auto fill, the next values it gives are
=IF(ISBLANK(SHEET1!A3),"",A3)
=IF(ISBLANK(SHEET1!A44),"",A44)
Instead of the
=IF(ISBLANK(SHEET1!A85),"",A85)
=IF(ISBLANK(SHEET1!A127)"",A127)
....
that I want it to give.
is there anyway to get the values to go up like I want them to, or do I have to enter them all individually? That would really not be fun for me, so I hope that's not the case.
View 4 Replies
View Related
Jan 20, 2008
Here is my test example I am trying to figure this out with.
Sub a()
Dim rg As Range
'set formulas to be filled
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF('sheet2'!RC="""","""",'sheet2'!RC)"
Selection.AutoFill Destination:=Range("A1:L1"), Type:=xlFillDefault
'now get autofill to populate until no more data on sheet 2
Set rg = [a2]
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
rg.Cells(2, 2).AutoFill Destination:=rg, Type:=xlFillDefault
End Sub
Without VBA, once I set the formulas for A1:L1, I can use the mouse to autofill all columns until Im around row 27,000.
Id like to be able to run a check for the end of data in sheet 2 so that if the sheet2 changes, it will all be included.
The resulting sheet1 cannot have any null values, so a perfect end of data check is necessary.
View 9 Replies
View Related
Jul 30, 2009
Copy and paste the formula into a range or paste a formula into the first cell in the range and autofill down for remainder of cells?
P.S. when autofilling down, can I specify somehow for it to stop at the last row with data in adjacent cell?
View 9 Replies
View Related
Feb 19, 2010
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
View 6 Replies
View Related
Sep 2, 2013
I am using Windows 7, with Excel 2010.
I have one Worksheet Short Course - PB's Which contains all swimmer information and searches through all previous swims and reports back the swimmers current Personal best times (PB)
I am creating a work sheet to calculate percentage increases over a set date period. I have managed the calculations but can not get the autofill to function as I was hoping.
In my short course sheet 1 Row = a Swimmer and there details
In my new sheet, I have 3 rows for the same swimmer
Row 1= Swimmer and PB's before a set date
Row 2 = Swimmer and PB's After set date and upto Todays date
Row 3 = Percentage calculation of difference between the two rows to enable track performance increase
I have all of this working and in place and want to copy the formula's down now to cover all swimmers in the club.
When I copy the 3 lines down, Autofill adds 3 to the row reference for the first line and I just want it to add 1.
ROW3) =IF('Short Course - PB''S'!A3="","",'Short Course - PB''S'!A3)
ROW4) =A3
ROW5) = A3
[Code].....
View 4 Replies
View Related
Nov 14, 2008
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
End Sub
View 9 Replies
View Related
Feb 5, 2009
I am having trouble filling a formulae series to the left on one spreadsheet, the fomulae being references to another sheet.
For example, I have two sheets 'Mtce Options' and 'Base Case'. In 'Mtce Options' I have the following formulae
A B C
1='Base Case'!A15='Base Case'!D15='Base Case'!G15
I want to fill to the left, incrementing the column references by a factor of 2 each time, eg. next two should be ='Base Case'!J15 and ='Base Case'!M15.
However, if I autofill to the left by highlighting A1, B1 and C1 or just B1 and C1 all I get is an inappropriate reference such as ='Base Case'!D15 or ='Base Case'!F15, respectively, in D15.
View 2 Replies
View Related
Sep 30, 2006
I am using the Networkdays function to calculate the number of working days between a date entered in A1 and a date in B2 and I have a range of holidays called 'holidays' and then have the result placed in E1.
View 9 Replies
View Related
Nov 3, 2009
I am trying to simplify my data entry and I have this idea that I do not know how to tackle, but it would be nice to have it worked out.
I am looking for an autofill macro that will fill column B2 to Bxx with values from 1 to max value specified in cell B1. Lets say I know I have 5 trees I need to enter. I want to enter "5" in a cell as a reference, and everything else will be filled in automatically with press of the button.
To make things even more productive I would like to use value from cell A2 and replicate it through the range specified above in column A2:Axx.
Result: ....
View 6 Replies
View Related
Mar 13, 2009
I have this ActiveCell.Offset(0, -1).Range("A1").Select. Selection.AutoFill Destination:=ActiveCell.Range("A1:A6"). However I need to copy the value down, so it is identical to the Range("A1"). At the moment it adds one year to each value when copied down. e.g. A1 = 12.01.09. and it copies it down so I get 12.01.10, 12.01.11 etc... I want them all to be 12.01.09
View 3 Replies
View Related
Nov 13, 2009
I am looking for a way for my macro to select cells C4:T4 and then auto fill the formulas down to the last row with data. I have found suggestions when working with one cell to auto fill, but cant seem to find a way to do it with a range of cells
View 2 Replies
View Related
Dec 29, 2009
I have a Macro and I need to code it to Autofill a formula in column B with data from A and I need to set it to AutoFill to the last row. Probably basic but I've tried a couple posts previously and I am just not getting it. This is what I currently have. Sorry, I recorded it and have some additional steps in here I don't need.
View 6 Replies
View Related
Apr 23, 2009
I have a piece of code that includes the following line:
Selection.AutoFill Destination:=Range("B1:B9414")
I want it to always autofill to the last row that has content in it. I have been changing the value to a large number that I know is always more than the content of the worksheet which changes periodically, but I would rather it always only go to the last row that is not empty so I don't have to go back and delete those extra rows.
View 9 Replies
View Related
Dec 27, 2008
I have a spreadsheet with data where the number of rows change daily, in column D.
I have formula in column E which I autofill manually by double clicking on the cross thingy of the first cell.
However I'm trying to do it using VBA, but it always stops at a certain row and not where the column D data stops.
Below is the macro which stops autofilling at row E7762
Selection.AutoFill Destination:=Range("E3:E7762")
Is there a way to amend the VBA to autofill based where Column D data stops?
View 9 Replies
View Related
Apr 24, 2009
I have a piece of code that includes the following line:
Selection.AutoFill Destination:=Range("B1:B9414")
I want it to always autofill to the last row that has content in it. I have been changing the value to a large number that I know is always more than the content of the worksheet which changes periodically, but I would rather it always only go to the last row that is not empty so I don't have to go back and delete those extra rows.
View 9 Replies
View Related
Oct 24, 2008
I need to write a code that will autofill down column A until the next value is reached and will continue to fill down using the new value. This will continue until the last row in column B. I have attached a small sample file for clarification.
View 3 Replies
View Related
