I would like to be able to use autofill from a vertical range to a horozontal one. In the example, if you click and drag on cell "R3" (=M3) it samples to cell dirrectly below it "M4", when I want it to sample the cell M6, M9 ect,ect. Is there a way to program the autofill to skip cells?
View 2 RepliesHere My first thread autofill horizontally from other sheet vertical data.
Vertical Data
Sheet1
A1
A2
A3
A4
& so on
AUTOFILL HORIZONTAL DATA
Sheet2
D3,D4,D5,D6..... fill by A1 A2 A3 A4.....
So I am putting togther a process matrix template for a Value stream analysis. I would use brute for and simple tricks to make this work but I am handing it off in two weeks to a student and I want this to be as strait forward and as easy to use as possible.
________C_______D_______E________F______G___
_____Part#1___Part#2___Part#3___Part#4___Part#5
Drill 1___1_______2________0_______3_______0__
Drill 2___0_______3________0_______1_______1__
Drill 3___0_______0________0_______4_______2__
Lathe 1__0_______1________1_______0_______0__
Lathe 2__1_______4________4_______0_______1__
Lathe 3__0_______0________5_______1_______0__
The first worksheet has a list of about 180 machines running vertically, and enough space for 20-50 part numbers to plugged in running horizontally across the top. The next three worksheets have the machines organized and grouped together by processes. I planned to use an Index-Match such as =INDEX('Individual Machines'!D$6:D$194,MATCH("Drill 1",'Individual Machines'!$C$6:$C$194,0))
The goal would be to get the user manually fill in the first worksheet, and then allow the user to use the vertical autofill function to fill in data on the later worksheets. This would provide a flexible template that can be re-used for different and various product families.
So populate something like this with a downward autofill for the number of times the part# hits a machine.
_______Drill 1___Lathe 2___Lathe 3___
Part#1___1________1________0____
Part#2___2________4________0____
Part#3___0________4________5____
Part#4___3________0________1____
Part#5___0________0________1____
I have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.
View 2 Replies View RelatedSimple transpose question: Lets say I have a verticle range of data from B3:B13. I wish to have code that will transpose that data into a horizontal range at D3:M3, is that possible?
View 5 Replies View RelatedCurrently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
[Code]....
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?
I have a massive list of items, most of the same items have different detail depending on the item itself and/or region its being sold etc. I'm been using combo boxes to change regions giving the full range of items within that region but different prices etc.
The problem i'm having with combo box or even vlookup is I can only search or change details in rows (row number), and not able to figure out how to change details within a set of columns (vertically). I want to be able to select the region via combo box and change the details within a set of columns (top to bottom).
I am trying to create a formula that will sum a range based on both horizontal and vertical criteria using the following data:
1
2
1
20%
25%
2
40%
25%
3
40%
50%
For example, if the horizontal criteria were "1" and the vertical criteria were "2", I need to sum the 20% and 40% amounts (60%). If the horizontal criteria were "2", and the vertical criteria were "3", I would need to sum the 25%, 25%, 50% (100%). I have tried using SUMIFS functions, but they do not appear to work with both horizontal and vertical axis criteria (I receive a #VALUE error).
I am trying to compare vertical range "A" in sheet1 to vertical range "K" in sheet2 if they match to copy both rows and place in sheet3. I have tried the following but it did not work.
Sub Searching()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
' create short references to sheets
' inside the Sheets() use either the tab number or name
Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets(2): Set sh3 = Sheets(3)
[Code] ......
I am trying to devise a formula for the data set below (into a different tab). The table below is something like what I have:
Day 1
Day 2
Day 3
Day 4
[Code]....
Though my table is a bit more complicated (it has empty rows in between and a much larger data range).
I need to reflect this in another tab in the following format:
Day 1
Day 2
Day 3
Day 4
Spoke 1
Spoke 2
Spoke 3
As I fill in data in the first tab, I would like this tab to automatically pick it all up. Is there any way I can do this? I may add rows etc. so I do not want a simple addition.
I have multiple worksheets each with varying amounts of data. I have a number in cell A1 that I need to autofill down to the bottom of each worksheet. All of which have varying amounts of data.
RA-0151
0
765804
3308345
168
0
5.74E-07
8.8E-09
1.371696
0
0
[Code] ........
When I record a macro it sets the autofill range as the bottom of what ever worksheet I recorded it on.
How can you write a macro that goes to the last row of each worksheet.
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A122"), Type:=xlFillCopy
Range("A1:A122").Select
Range("B1").Select
End Sub
I am trying to autofill cells across a column that sum a range but move over 5 columns from another worksheet. for example, i want to take this range:
Schedule!AG6:AK6 and when I autofill, the next adjacent column will fill with Schedule!AL6:AP6, the next with Schedule!AQ6:AU6, and so on.
I have tried offset, but am not sure if that's the right formula.
I am using the following code to Autofill a formula through a variable range:
Selection.AutoFill Destination:=Range(MyFormulaCell, TerminalCell), Type:=xlFillDefault
MyFormulaCell is the cell with the formula, and TermnialCell is a variable set on a count of rows. This is working great, and I can loop through this for the columns I need to copy just fine by incrementing values, but as I apply this to several sections, it's taking me up to a minute to run the macro. In this "I want it now" day and age, I'm sure my coworkers for whom I am making this will not be happy to wait that long.
Is there a way that I can incorporate a selected range into this method?
I'd like to set MyFormulaCell to:
Range(Selection, Selection.End(xlToRight)).Select
and then autofill the formula down the number of rows contained in TerminalCell.
I have worksheets that refernce values in one column off columns in others and do this on daily basis. each day there are blank amounts in random fields which I inturn use a macro to delete rows with blank amounts
I then want to autofill the numbers from 0001 to the bottem line of the sheet where the word "END" always is, as the position of the end word is random each time and autofill always needs a definite range how do I get my macro to autofill down to the word "end" ?
here is the code that doesnt work for me and I've been trying to fix
Sub Macro1()
For counter = 1 To 30
Set curCell = Worksheets("Sheet1").Cells(counter, 6)
If curCell.Text = "END" Then Range(Cells(1, 6), Cells(counter-1, 6)). _
Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlDay, _
Trend:=False
Next counter
End Sub
I am creating a loop that will autofill 16 cells down. It copies the formula from one cell (Z230), pastes that 16 cells down (cell Z246), then changes the lock properties of the cell (Z246) before autofilling down 16 (to cell Z261). At which point the loop starts over again. (copies cell Z246,pastes it to Z262, fills down 16 , etc)
Because the cell I am copying from changes in each loop, I set a variable to grab the address of the starting cell and last cell of the copy.
I then put this into a String so it would return something like this
Range("Z245:Z259")
Problem is I get extra quotes around Range("Z245:Z259") - it comes out as string "Range("Z245:Z259")"
This gives me a problem when I try to replace
Selection.AutoFill Destination:=Range("Z245:Z246"), Type:=xlFillDefault
with
Selection.AutoFill Destination:=rangevariable, Type:=xlFillDefault
Below is my total code, but I get stuck on the selection line. I have to do this 300 times so I would prefer not to do it manually!
I have 2 sheets - 1st sheet holds the data that is updated periodically. 2nd sheet pulls the data into A2:k2 and formats it for importing to another program. I need a macro that will copy the data from A2:K2 and auto fill down all rows from sheet 1 that contain data. I have read previous threads for autofill with no success using my below code.
Sub FillDownToLastUsedCell()
Sheet 'RawDataA2:K2' Range("A2:K2", Cells(Rows.Count, "Sheet 'RawData'A2:K2").End(xlUp)).FillDown
End Sub
I need to autofill a range of formulas (A2:O2) but the actual range to fill up depends on the range of data on sheet1. I've managed to use:
View 4 Replies View RelatedI have the following series of formulas consecutively in a single column:
=IF(A3=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A4=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
.
.
.
=IF(A50=INDIRECT(ADDRESS(2+MATCH(MIN(H3:H50),H3:H50,0),1)),"YES","NO")
=IF(A51=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
=IF(A52=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
.
.
.
Effectively, I am comparing the values in each set of 47 to determine which one is the smallest. The smallest entry will get a YES label next to it and all others will get a NO. The problem I am having is the the autofill feature is not correctly detecting the pattern, even if I manually fill in the first 49 entries (shown above). If I hilight all of the above entries and autofill one down, I get the following:
=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H53:H100),H53:H100,0),1)),"YES","NO")
It should be:
=IF(A53=INDIRECT(ADDRESS(2+MATCH(MIN(H51:H98),H51:H98,0),1)),"YES","NO")
Basically I need the H:H: reference to stay constant, but only for 47 cells at a time. After that, both the lower and upper bound should increase by 47. Is there any way to indicate this to Excel? Manually typing in the new formula every 47 entries isn't practical because the spreadsheet has thousands of entries. How I can accomplish this using a single formula and autofill?
I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:
Code:
Sub SummariseSheets()
'collates individual client development data from consultant
'worksheets and compiles in one sheet on summary page
[Code]....
The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.
In a macro - how can you copy a cell and keep a data range absolute?
In the statement below, the range denoted by: DATA!RC[-52]:R[829]C[-52] is D2:D829. When I use the macro to Autofill using the following:
(Selection.AutoFill Destination:=Range("BG2:BG220"), Type:=xlFillDefault)
the range progresses. I need to have the range as an absolute.
Sheets("Report").Select
Range("BG2").Select
Selection.FormulaArray = _
"=SUM((DATA!RC[-52]:R[829]C[-52]=""APPOINTMENT"")*(DATA!RC[-55]:R[829]C[-55]>=Report!RC[-1])*(DATA!RC[-55]:R[829]C[-55]
How can I auto fill a range while leaving the cell number the same?
Code: =IF(AND(B4>=c2,b4=c2,b4=c2,b5=c2,b6=c2,b7=D2,b4=E2,b4=F2,b4=G2,b4=H2,b4
Excel 2010
I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.
Selection.AutoFill Destination:=ActiveCell.Range("A1:A50000")
ActiveCell.Range("A1:A50000").Select
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
how do you create a graph with a field such as vendors on the X-axis, but with dollar values spent on the left side of the y-axis and percentage of total dollars spent on the right side vertical axis?
View 3 Replies View RelatedI recorded one for an autofill series which basically came out as
View 4 Replies View RelatedFor our attendance register, the master data table holds a list of all club members (one member per row).
The master data table consists of formula columns, data columns, and two data columns with drop-down list validation.
I'm using this
I want to have a range of days in a cell, for example in cell A1 will have may 2-may 9. when I use the autofill handle I want A2 cell to read may 10 - may 17 and then if I use it again A3 will read may 18- may 25 and then roll over to the next month . As it stand right now if I put in may 2 - may 9 and use the autofill handle the next cell down will read may 2 - may 10.
View 2 Replies View RelatedAs part of a larger Macro I have the following Code:
Range("X2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0,""Yes"",""No"")"
Range("X2").Select
Selection.AutoFill Destination:=Range("X2", "X" & lastRow)
Range("X2", "X" & lastRow).Select
Selection.Copy
The code works fine when I have more than two rows of data that I am manipulating but gives me an Run Time Error - AutoFill Method of Range Class Failed at the following line:
Selection.AutoFill Destination:=Range("X2", "X" & lastRow)
When I have only one row of data I get the Run time error.
Any code that will allow me to process the data if I have one row of data as well.
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?