Fill Down based on count in another sheet

I am using the following in Excel to copy a formula:

ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown

I need to only copy the formula for a certain number of rows up to 2000.  
The number of rows I need is based on the number of rows I have in worksheet 
"Results Data" in column A.  So, if "Results Data" has data in column A2:A16, 
I will need to copy down the formula for all columns in worksheet "Results 4" 
to column 17.
0
Utf
4/27/2010 3:27:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1029 Views

Similar Articles

[PageSpeed] 19

Sub FillFown()
Dim lStop As Long

With Worksheets("Results Data")
   lStop = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Worksheets("Results 4").Range("3:" & lStop).FillDown
End Sub



-- 
Regards
Dave Hawley
www.ozgrid.com
"LostInNY" <LostInNY@discussions.microsoft.com> wrote in message 
news:5383DC34-ED60-4868-8AD5-C589640B635C@microsoft.com...
>I am using the following in Excel to copy a formula:
>
> ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown
>
> I need to only copy the formula for a certain number of rows up to 2000.
> The number of rows I need is based on the number of rows I have in 
> worksheet
> "Results Data" in column A.  So, if "Results Data" has data in column 
> A2:A16,
> I will need to copy down the formula for all columns in worksheet "Results 
> 4"
> to column 17. 

0
ozgrid
4/27/2010 3:42:57 AM
Works great Dave Thanks!!

"ozgrid.com" wrote:

> Sub FillFown()
> Dim lStop As Long
> 
> With Worksheets("Results Data")
>    lStop = .Cells(.Rows.Count, "A").End(xlUp).Row
> End With
> Worksheets("Results 4").Range("3:" & lStop).FillDown
> End Sub
> 
> 
> 
> -- 
> Regards
> Dave Hawley
> www.ozgrid.com
> "LostInNY" <LostInNY@discussions.microsoft.com> wrote in message 
> news:5383DC34-ED60-4868-8AD5-C589640B635C@microsoft.com...
> >I am using the following in Excel to copy a formula:
> >
> > ThisWorkbook.Worksheets("Results 4").Range("3:2000").FillDown
> >
> > I need to only copy the formula for a certain number of rows up to 2000.
> > The number of rows I need is based on the number of rows I have in 
> > worksheet
> > "Results Data" in column A.  So, if "Results Data" has data in column 
> > A2:A16,
> > I will need to copy down the formula for all columns in worksheet "Results 
> > 4"
> > to column 17. 
> 
0
Utf
4/27/2010 4:18:01 AM
Reply:

Similar Artilces:

How do I count something that has two fields that must be true
I am working to count items in a spreed sheet that has several fields on it. I want to count how many items have an item number of "4016" and a price of "100". Both fields must equal true for it to count them. Thank you, Charles =SUMPRODUCT(--(B2:B100=4016),--(C2:C100=100)) -- HTH RP (remove nothere from the email address if mailing direct) "Charles" <Charles@discussions.microsoft.com> wrote in message news:EC3C74A7-BAB7-4F98-853B-F495C12B33F8@microsoft.com... > I am working to count items in a spreed sheet that has several fields on it. >...

Hyperlink base in Excel 2007
Where in Excel 2007do I set a hyperlink base? Should there be no built- in procedure to do so, is there a makro available? Thanks, Sven What do you mean by "hyperlink base" ? Do you just mean how to add a hyperlink to a cell or some text? If so, the click the Insert tab on the Ribbon, move to the Links group on that tab and click the hyperlinks tool best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sven Berg" <wiesel69@gmx.de> wrote in message news:0b88bf9e-f4ae-4c90-a22b-38cbde14509c@a3g2000prm.googlegr...

Filtering Based in a cell value
I have a list which I want to filter based on the value of a cell that is not in that list. Any ideas? eg List of values to be filtered in Column X aaa aaa bbb bbb ccc ddd eee fff ggg The value I want to filter by in A1 derived from Data validation list on separate sheet aaa I want to filter out all the values that are not aaa TIA Steve How about a helper column with a formula like: =a2=sheet2!$a$1 (dragged down) Then you can filter on True/False for that column. Steve Wright wrote: > > I have a list which I want to filter based on the value of a cell that is > not...

Filling cells in between pairs of numbers
Hello all, I am trying to find an easy way to fill in cells in between pairs of numbers each of which is exactly the average of the pair's difference averaged by the number of these cells greater than the previous. So I have 1 1 2 3 4 8 5 6 7 8 20 9 10 12 13 14 15 45 I want: 1 1 2 3.333333333 3 5.666666667 4 8 5 11 6 14 7 17 8 20 9 23.57142857 10 27.14285714 11 30.71428571 12 34.28571429 13 37.85714286 14 41.42857143 15 45 I know I can do a Fill, Series, but then even with a macro that does a "control shift up fill series enter",...

Case filled out from SharePoint
I need to have a simple form my users fill out to submit a case. This would include the Business Required fields on the CRM Case form. Is there an easy way to do this using SharePoint? In other words, the user would go to the SharePoint site, click a link to "Submit a Support Request". This would open a form (Infopath perhaps?) where they fill in a few simple items, click submit, and a new CRM Case is created. This then would fire any pertinent workflows as if the case had been created from within CRM itself. I'm aware there are 3rd party solutions out there but would...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

From field filled in
Hi and thanks in advance for this help. I am using Office 2003 with Exchange 2003. I have a second mailbox setup in my profile. When I reply to an e-mail sent to the other inbox, the From field is automatically filled in with that name. So for example, if the other inbox is "HelpDesk" and I reply to an e-mail in that inbox, the from field is automatically filled in as "HelpDesk". Hope that makes sense. Thanks!! <twytch111@gmail.com> wrote in message news:1175611189.053649.28340@q75g2000hsh.googlegroups.com... > Hi and thanks in advance for this help. I am...

In 3D ref. can non-contiguous sheets appear? #2
I know that Sheet1:Sheet3!A1:F1 can be named. But Can I have Sheet1 & Sheet3 (excluding sheet2 ?) If so, what would be the notation? Thanks Drag the Sheet2 tab to the *right* of the Sheet3 tab, OR Drag the Sheet2 tab to the *left* of the Sheet1 tab. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "dindigul" <padhye.m@gmail.com> wrote in message news:eTOoq6EsHHA.4860@TK2MSFTNGP02.phx.gbl... I know that Sheet1:Sheet3!A1:F1 can be...

Ref cell in another worksheet
Hi All Sorry for the very basic question. I have several worksheets in a workbook and have named the first sheet 'Info'. This sheet holds such data as 'contract number' and 'user name'..... This data is required on all the other sheets but i would like to just reference the data from the 'Info' sheet What do i have to enter on the other sheets so that this info only has to be entered on the 'Info' sheet How do i reference this data/info from the 'Info' sheet in the header/footer of printed sheets Thanks for any/all help Sal Select the sheet...

Vlookup based on Active cell
Is there a way to have a vlookup formual based on the data in the active cell. I have data on one sheet that I what to pull in to a cell on another worksheet based on the data in the active cell I click on. I looking for the reference of the vlookup to be the active cell. thanks in advance for any help on this Thomp <williamth...@gmail.com> wrote... >Is there a way to have a vlookup formual based on the data in the >active cell. I have data on one sheet that I what to pull in to a cell >on another worksheet based on the data in the active cell I click on. >I looking for the...

Colours changing in the fill palette and the spreadsheet?
Hi I have a user with Office 2003, and her pc and build is standard as with her colleagues. In Excel the colours would change (cells filled) also if the user goes to her pallette the colours have also changed. Desktop and all other applications colour are fine. The following resolutions have been tried - office detect and repair, updates, asked the user to got to the tools options and reset the pallette and this does not work. She has to log off and back in again in order to set it back to the proper colours. Again this is only affecting Excel? Any pointers? I wish to thank you in advanc...

Need macro to print sheet with multiple repetitions
I have an excel-based sales report with charts, etc. The territor number in cell A1 determines the data that populates the tables an charts. When the user selects a new territory number in cell A1, th tables and charts update. I need to print the report for every sales territory every month (5 each month.) I don't want to have to manually select the nex territory, print, select the next territory, print, etc. Is there a macro that can automate the process? :confused -- Message posted from http://www.ExcelForum.com Hi Bill This sub will change the value of A1 so all cells will b...

Have one fill to auto fill aother
My question is simple. I would like to have field 27 = field 25. So when someone enters the data for field 25, the same data auto populated in field 27. Any help is appreciated! Kaylen, In the CotrolSource for Field 27 type... =[field 25] ....assuming of that you are talking about a form in Microsoft Access or did you mean to post this question in the Excel newsgroup? -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Kaylen" <Kaylen@discussions....

Open another file in CRichEditApp
Hallo, I work on a small editor and use a MDI RichEdit. The editor can be started using a parameter to open a file. I use Createmutex() to make sure only one instance is running at a time and I can send messages to the (only) instance, they go to the CMainFrame-class. Now I do not only want to stop creating a new instance but give the new paramter to the running instance, especially to my RichEditApp so a function to load the file can be started there. I tried to use a handle in the CMainFrame like this: CMyRichEditApp* myapp; myapp->OnLoadFile(); This does not work, it seems that the Ap...

Operating fill handle and moving entire cells with keyboard
Is there any way of operating the fill handle or moving entire cells/range of cells with the keyboard rather than using the mouse (drag and drop). Thanks, Tom. Tom Copy or Cut Select range Select top left corner of range. Hold Shift & press End, Arrow down or right. Copy:= Ctrl + C Cut:= Ctl + X Paste Ctl + V To Fill Down or Right Ctrl + D or ctrl + r Use Excel Help and search for keyboard shortcuts for a complete List. Regards Peter >-----Original Message----- >Is there any way of operating the fill handle or moving >entire cells/range of cells with the keyboard rathe...

Create field from append query based on linked table name
Here's the setup: Two linked tables called 'PHD' and 'XANS' bring in daily data from two CSV files. A union table-query puts the common data in both into the same name fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a new table called 'SOLS_MAIN' and I ran an append query called 'SOLS_DATA_APPEND' to append the data in the table-query, 'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for this was so that I could assign my data a primary key. Even though I have achieved my goal of merging the da...

How do I auto fill long col. filling blanks with last filled cell
On a large worksheet, is there a way to fill the blanks in a column with the last filled cell without using the autofill handle? My worksheet is over 2000 rows and I am hoping to find some way to mass fill the blanks in Excel. Here is a small example of what I am talking about. Thanks for any tips. S1475 201686 506456 S1888 38254 15623 56731 S9564 46321 156323 M, This ought to work... http://j-walk.com/ss/excel/usertips/tip040.htm Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "MBBeginner" <MBBeginner@discussions.microsoft.com> wrote in m...

distinct count summarize Data for pivoit table
I hope the Excel pivoit table able to distinct count summarize Data ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=b8e39814-c8a4-4b0d-9a7a-1eff8605f9a5&dg=microsoft.public.excel.misc Until ...

a MFC based sample for listview ?
Hi, Can somebody point me to a complete working MFC sample which does this --- 1. I have a TREE of objects like class Acpi_Dev_Object { int HID ; int UID ; char func[]={ "a string not more than 500 characters, but variable length known at run time only" } } 2. I want to display one object at a time which the user might have selected from the view, e.g windows file explorer 3. user should be able to delete, add or insert these type of objects A search on the net yielded some, but none which exactly does this quickly w/o major modifications...

macro to compare info in another file
Hi, i have 2 excel files. file1 with a list of names and file2 with a list of names and their age. Is it possible to write a macro whereby it will read the name in file1 and serach for its age in file2? thanks Hi Lynn a non macro solution could be the use of VLOOKUP. Assumptions: - column A in both workbooks contains the name - column B in file 2 the age try the following formula in B1 in the first file: =VLOOKUP(A1,'C:\temp\[file2.xls]Sheet1'!$A$1:$B$1000,2,0) and copy down (you have to adapt the path and filename as well as the sheet name) -- Regards Frank Kabel Frankfurt, Germ...

Counting something like 430<x<=440
I tried =countif(B5:E29, "430<x<=440") but I always get 0, but I ca clearly see that there are values that fit those criteria. Any ideas -- infinite_zero0 ----------------------------------------------------------------------- infinite_zero00's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2815 View this thread: http://www.excelforum.com/showthread.php?threadid=47665 =countif(B5:E29, ">430")-countif(b5:e59,">440") -- duane ------------------------------------------------------------------------ duane's Profil...

COUNTIF to count last 4 letters in string
I'm trying to figure out how to count the occurences of the text "cnty" in a column of text. "cnty" will always be the last 4 characters to the right. Can anyone help me here? Thanks in advance. You can use the formula (adjust the range to suit your circumstances... =SUMPRODUCT(--(RIGHT(A1:A100,4)="cnty")) Rick "davegb" <davegbel@comcast.net> wrote in message news:59239653-1dfe-4a18-bce7-80593eccd972@c19g2000prf.googlegroups.com... > I'm trying to figure out how to count the occurences of the text > "cnty" in a column ...

Query to show latest price based on dates
I have a shop database that hold products and the prices we purchase items for tblProducts ( ProductID, ProductName) tblStockPurchasePrice (ID, ProductID,Price,PurchaseDate) I would like a create a query that will show ever product along with the latest purchase price based on the PurchaseDate. Could any one let me know how to do somthing like this See: http://www.mvps.org/access/queries/qry0020.htm Group by the product, get the most recent date, and then use the subquery to get the price for that product on that date. If subqueries are new, here's an intro: ...

Criteria on Query based in a list of another Query
I have a Query Named: WODispatchLinkedtoExcel_QueryCurr and I have many datas on it, and a column named [Planner] also I have another query named: Query_ActivePlanners and a column named [Planner_Code] where it shows Planner Code availables.. What I want is to show in the first query only those datas which the value is in Query_ActivePlanners I have this criteria.. WHERE ((lbl_PlannerCodes.Planner_Code) Is Not Null)); but it shows all datas. please help Thanks -- Lorenzo Díaz Cad Technician In WODispatchLinkedtoExcel_QueryCurr, use the Add Table dialog (toolbar) to add Query...

Copy from one worksheet to another
I have some code that calculates how many lines I need to copy from one worksheet to another, but when I do the copy I get an errormessage. Here is part of my code: Private Sub Start_Click() Dim lastline As Integer Dim MalWb, RappWb As Workbook Workbooks.OpenText Filename:=RapportFrm.Innfil.Value, Origin:=xlWindows, StartRow:=1, _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _ :=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _ Other:=True, OtherChar:="""", FieldInfo:=Array(Array...