Autofill For Unknown Range In Different Sheet.
Jun 16, 2009I 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
ADVERTISEMENT
Summing Across Unknown Range In Unknown Cell
Oct 26, 2009I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
Define An Unknown Range
Aug 13, 2006is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown
Identifying An Unknown Range
Mar 11, 2007I'm trying to set a range to a particular cell in Col D but I don't know the row number because this can vary depending on the amount of data entered. At present my code looks like this
Dim MyCount As Integer
Dim r1 As Range
'No of rows in Col D
MyCount = Range("A2:D2", Range("A2:D2").End(xlDown)).Rows.Count
Set r1 = Range(Cells(MyCount, 4))
The problem is with my line
Set r1 = Range(Cells(MyCount, 4))
Modify An Unknown Range Of Cells
Jul 25, 2007i have a list of countries in a column and have to go through this column ensuring they are in the correct format. I'm struggling to figure out how to iterate through each row of the column, test it's value and then change if necessary...
for example
column a --> column A(modified)
Usa --> USA
Ukraine - Mobile (Umc) --> Ukraine - Mobile (UMC)
Uk --> UK
Uruguay --> Uruguay
so values in column a becomes like those shown above.
Add Data From Unknown Number Of Sheets To Summary Sheet
Aug 1, 2008How to I add data to an existing summary sheet, in a specific cells, from an unknown number of sheets?
Tickets will be generated with unknown names.
I want the summary sheet to be able pull the ticket numbers into the top row of the summary sheet, to an undefined number of sheets.
As well I would like the summary sheet to pull the job# on each ticket and the hours for each person on each ticket.
Range Of Cells Of Known Columns But Unknown Rows
Mar 19, 2008I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.
What I've managed to find so far on the forums is
Offset("$A$3", 0, 0, (Match(9.99999999999999E+307, "N:N", 1) - 1), 14).Select
which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).
VBA Code To Set Unknown Range Based On Current Date In 1 Column
Jul 21, 2009i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies View RelatedMacro To Change Range / Action Depending On Worksheet (range To Autofill)
Sep 17, 2013Currently 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]....
Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?
Feb 19, 2010I 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?
Autofill To Last Row In Range
Sep 27, 2013I 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
Autofill Data In A Column From One Sheet To Another
May 29, 2014I'm trying to autofill data in a column from one sheet to another in the same workbook. My goal is to be able to paste data in one sheet and have it appear in another sheet.
View 2 Replies View RelatedAutofill Last Sheet Save Date
Oct 29, 2012Is there a formula I can use to see what the last time the sheet was saved is without using visual basic
View 1 Replies View RelatedAUTOFILL SHEET 2 Update As Data Goes In
Jan 11, 20091.
column b should autofill the last number in column i
in this case b12 = £1820.58
and then evertime column i has data in it should go to the next row in column b
2.
i would like column d to be blank until there is a stake in column c
3.
the same for column f, h and i
4.
column l should have the date auto entry after column c has data input
5.
column n should automatically work out how many days this has been running
6.
sheet 2 cell g5 should know how many records are in sheet 1
7.
sheet 2 cell g6 should return all known "y" in sheet 1 column g
and g7 should return all known "n" in sheet 1 column g
column i.........................
Autofill Range From Another Worksheet?
Mar 24, 2014I 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.
Autofill Using A Selected Range
Jan 4, 2008I 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.
AutoFill With Variable Range
Nov 1, 2006I 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
Use Variable For AutoFill Range
Oct 12, 2007I 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!
Copy Range & AutoFill To Last Row
Jan 26, 2008I 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
Autofill Cells With Information Contained In Other Sheet
Feb 9, 2014I have a workbook with two sheets. The first one, let's call it the Main sheet, will be used to keep track of the touring artists of a record label. The second sheet contains a list of show venues with their respective contact info. Here's what the two sheets look like :
Main sheet
mainsheet.jpg
List of contacts
listofcontacts.jpg
What I would like is row D of the Main sheet to autofill, according to the contact information contained in row B of the List of contacts, when I enter a venue name in row C of the Main Sheet.
Horizontal Autofill From Vertical Data Different Sheet
May 21, 2013Here 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.....
AutoFill Cell W/ Data From Different Sheet After Action
Jun 22, 2009I have an excel file that contains two sheets. Sheet2 contains rows of different types of products with each of the columns containing details about the product.
On Sheet1, I have setup a data validation list in which a certain cell contains a drop down box that selects a product from the first column of all the rows on t from Sheet2. I used
AutoFill Column To New Sheet Based On Value - Autoupdate
Mar 31, 2014Master Template
Project 1
Project 2
Task 1
1 - Mar
28 - Dec
Task 2
2 - Mar
2 - Jan
Project Due
5 - Mar
4 - Jan
Above is a table we have for high level tracking on specific tasks due for projects.
What I want to do is: If the last cell in a column = Jan then it will automatically copy the whole column and insert it into a Sheet named January and have it automatically update. Below is the end result.
January Template
Project 2
Task 1
28 - Dec
Task 2
2 - Jan
Project Due
4 - Jan
AutoFill Formula Based On Data In Another Sheet
Jan 20, 2008Here 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.
Autofill: Vertical Range To Horozontal.
May 12, 2009I 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 Replies View RelatedAutofill Not Detecting Range Pattern?
Apr 10, 2013I 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?
Autofill Range Using VBA Based On 2 Variables
Jan 14, 2014I 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.
Absolute Range - AutoFill Via Macro
Jul 1, 2009In 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]
Autofill Range While Leaving The Cell Number Same?
Jul 30, 2012How 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 :: Autofill Ever Changing Range?
Jan 23, 2013Excel 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