Copy formulas #3

Hello,
I have a forumla in a cell which I need to copy and paste to a variable
number of cell directly below.  Currently I have set up a little macro
that copies and pastes the formulas to the 250 cells below.  

Unfortunatley I never know how many cells the forumla needs to be
copied to.  What I really want to check whether cell A1 contains data,
if it does then pastes the forumala, then look at A2, if that contains
data then paste the forumla and so on until the cell checked is empty. 


I know this is possible but don't know how to do it.

Please help me.


-- 
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14501
View this thread: http://www.excelforum.com/showthread.php?threadid=469295

0
9/20/2005 7:46:46 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
446 Views

Similar Articles

[PageSpeed] 58

If your data is in column A and your formula is in column B, simpl
double click on the small square in the lower right corner of the cel
pointer.  This will auto fill your formula down only as far as there i
data in column A

HTH

Bruc

--
swatsp0

-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510
View this thread: http://www.excelforum.com/showthread.php?threadid=46929

0
9/20/2005 8:14:22 PM
Hi,
Thanks for the suggestion I never knew you could do that.
Unfortunately my data is not in adjacent cells, so this won't work fo
me.

Any further suggestions??

Thanks
Simo

--
sgrec
-----------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1450
View this thread: http://www.excelforum.com/showthread.php?threadid=46929

0
9/20/2005 8:25:34 PM
c'mon guys there must be somebody out there who knows the answer to
this.

Simon


-- 
sgrech
------------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14501
View this thread: http://www.excelforum.com/showthread.php?threadid=469295

0
9/20/2005 9:26:38 PM
Option Explicit
sub TestMe()

 dim LastRow as long
 with worksheets("sheet1")
    lastrow = .cells(.rows.count,"A").end(xlup).row
    .range("x1:x" & lastrow).formula = "=yourformulahere"
 end with
end sub

I used column X and I didn't know what your formula is.

If you type your formula into X1, you could use that.

Option Explicit
sub TestMe2()
 dim LastRow as long
 with worksheets("sheet1")
    lastrow = .cells(.rows.count,"A").end(xlup).row
    .range("x1:x" & lastrow).formula = .range("x1").formula
 end with
end sub

sgrech wrote:
> 
> Hello,
> I have a forumla in a cell which I need to copy and paste to a variable
> number of cell directly below.  Currently I have set up a little macro
> that copies and pastes the formulas to the 250 cells below.
> 
> Unfortunatley I never know how many cells the forumla needs to be
> copied to.  What I really want to check whether cell A1 contains data,
> if it does then pastes the forumala, then look at A2, if that contains
> data then paste the forumla and so on until the cell checked is empty.
> 
> I know this is possible but don't know how to do it.
> 
> Please help me.
> 
> --
> sgrech
> ------------------------------------------------------------------------
> sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14501
> View this thread: http://www.excelforum.com/showthread.php?threadid=469295

-- 

Dave Peterson
0
petersod (12004)
9/20/2005 11:47:29 PM
Hi thanks for your reply - i should explain a little further

I actually have five forumlaS  that need to copied and pasted.  Th
formula's are contained within the cell range j11 to n11.  The formula
are

=IF(ISNUMBER(I11),IF(OR(H11="gbp",(RIGHT(A11,3)="fix")),(G11*I11)/100,(G11*I11)),"")
=IF(J11="","",IF(J11=0,"",(VLOOKUP(D11,'G:\XLDATA\OEIC\PRICING\[prices1200.xls]UT_Prices'!$A$2:$F$1000,6,FALSE))))
=IF(J11="","",IF((RIGHT(A11,3)="fix"),(G11*K11)/100,(G11*K11)))  
=IF(J11="","",IF(F11="B",L11-J11,J11-L11))
=IF(L11="","",(VLOOKUP(H11,'G:\XLDATA\OEIC\PRICING\[prices1200.xls]UT_Prices'!$A$2:$F$1000,6,FALSE))


Please can you explain what I need to do.
Thanks
Simo

--
sgrec
-----------------------------------------------------------------------
sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1450
View this thread: http://www.excelforum.com/showthread.php?threadid=46929

0
9/21/2005 7:19:01 PM
Does this mean the formulas start at row 11 (J11:N11)?

Option Explicit
Sub testme()

    Dim myFormulas As Variant
    Dim FirstCol As Long
    Dim LastRow As Long
    Dim iCol As Long
    Dim fCtr As Long
    
    myFormulas = Array("=IF(ISNUMBER(I11),IF(OR(H11=""gbp""," _ 
                           & "(RIGHT(A11,3)=""fix""))," _
                           & "(G11*I11)/100,(G11*I11)),"""")", _
                       "=IF(J11="""","""",IF(J11=0,""""," _
                           & "(VLOOKUP(D11,'G:\XLDATA\OEIC\" _ 
                           & "PRICING\[prices1200.xls]" _
                           & "UT_Prices'!$A$2:$F$1000,6,FALSE))))", _
                       "=IF(J11="""","""",IF((RIGHT(A11,3)=""fix"")," _
                           & "(G11*K11)/100,(G11*K11)))", _
                       "=IF(J11="""","""",IF(F11=""B"",L11-J11,J11-L11))", _
                       "=IF(L11="""","""",(VLOOKUP(H11," _ 
                           & "'G:\XLDATA\OEIC\PRICING\" _
                           & "[prices1200.xls]UT_Prices'" _ 
                           & "!$A$2:$F$1000,6,FALSE)))")
                       
                       
    With Worksheets("sheet1")
        FirstCol = .Range("J11").Column
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        fCtr = LBound(myFormulas)
        
        'this just does for icol = J to N
        'actually it counts the formulas (in case you add more, 
        'you don't need to change this line
        'and -1  +1 will add up to zero, but it's kind of nice for backtracking
        For iCol = FirstCol To FirstCol - 1 _
                      + UBound(myFormulas) - LBound(myFormulas) + 1
            .Range(.Cells(11, iCol), .Cells(LastRow, iCol)).Formula _
                   = myFormulas(fCtr)
            fCtr = fCtr + 1
        Next iCol
    End With
End Sub

Notice that the formulas are written for the first cell in the range (row 11)
and each double quote is doubled up.

sgrech wrote:
> 
> Hi thanks for your reply - i should explain a little further
> 
> I actually have five forumlaS  that need to copied and pasted.  The
> formula's are contained within the cell range j11 to n11.  The formulas
> are
> 
> =IF(ISNUMBER(I11),IF(OR(H11="gbp",(RIGHT(A11,3)="fix")),(G11*I11)/100,(G11*I11)),"")
> =IF(J11="","",IF(J11=0,"",(VLOOKUP(D11,'G:\XLDATA\OEIC\PRICING\[prices1200.xls]UT_Prices'!$A$2:$F$1000,6,FALSE))))
> =IF(J11="","",IF((RIGHT(A11,3)="fix"),(G11*K11)/100,(G11*K11)))
> =IF(J11="","",IF(F11="B",L11-J11,J11-L11))
> =IF(L11="","",(VLOOKUP(H11,'G:\XLDATA\OEIC\PRICING\[prices1200.xls]UT_Prices'!$A$2:$F$1000,6,FALSE)))
> 
> Please can you explain what I need to do.
> Thanks
> Simon
> 
> --
> sgrech
> ------------------------------------------------------------------------
> sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14501
> View this thread: http://www.excelforum.com/showthread.php?threadid=469295

-- 

Dave Peterson
0
petersod (12004)
9/21/2005 11:06:59 PM
Reply:

Similar Artilces:

MFC and threads #3
Greetings, I am running into a problem when trying to pause a thread in MFC based application. This is my first time using user defined Events for synchronization. The scenario is like this. Code executes in a worker thread and based on some conditions thread launches another new dialog window. That window displays some message to user ie, Hello There! in an Edit control. I provide a Resume button on the bottom of that message Window so that user can continue after reading the message and the worker thread should continue from where it left off. This is the code for that Resume button. v...

Copy and Paste not saving format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, I just bought Microsoft Office for Mac early this month and am still getting used to it. Can someone help me with this: I have several documents typed onto a notebook layout with lots of bullet points, color changes, cross outs and the like. I wanted to copy and past all of that into a new document but when I did ALL of the formatting changes were lost. There were no bullet points, so the text flushed left, yada, yada, yada. I looked around the toolbar to try and find a setting that would allow me to do the cut an...

Sample CRM 3.0 reports downloadable from Partnersource
It would be useful to have sample reports from CRM 3.0 availble to download from Partnersource to show customers what the reports look like. Customers won't have enough data in their own CRM for some time after going live, therefore they cannot easily get an idea of what information the standard reports in CRM can provide. This would be useful to Partners to use as a selling aid and in giving training. ---------------- 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" ...

CRM 3.0
Hi All, I am writing a custom report for CRM 3.0 to basically copy the My Activities view but display the regarding and To contacts and associated phone numbers. The report is basically done except for a few small issues. I would like to set up the Dynamic Drill-Through so when the person clicks on the Activity Subject it will open the associated Activity record. Unfortunately I have been unable to find the information needed to use in the following code to set the Object Type Code (OTC) to the correct activity type: = Parameters!CRM_URL.Value & "?ID={"&Fields!Activityid...

Send #3
Messages waiting to be sent are NOT formatted in italic in the Outbox, and therefore remain in the outbox after send/receive. Hi - please tell us your version of Outlook, what service pack you have, what mode if applicable (see all this in help | about) and what kind of e-mail account you use. Bernie wrote: > Messages waiting to be sent are NOT formatted in italic in > the Outbox, and therefore remain in the outbox after > send/receive. ...

Create Expression from Func? (3.5 SP1)
Hello: Is there are way to create an Expression from a Func? I don't like cluttering up my method signatures with the whole Expression<Func<TResult, T>> type. I would prefer for callers to simply see Func<TResult, T> instead. I'd like to take a Func and create an Expression from it. Is that possible? Thanks, Travis Parks Jehu Galeahsa wrote: > Hello: > > Is there are way to create an Expression from a Func? I don't like > cluttering up my method signatures with the whole > Expression<Func<TResult, T>> type. I would ...

photogallery in pub #3
How many default photos in photo gallery web page of publishers? Ans is 12 but please explain any body. Thanks in advance. ...

Calendar sharing #3
Hello all, the current exchange setup is as follows: 1. domain A.... is windows 2000, with exchange 5.5 and is hosted in Organization A 2. domain B .... is windows 2003, with exchange 2003 and is hosted in Organization B Is there a way for users from both domains to access the other's calendar ( for example, user 1 in domain A wants to access user 2 in domain B)? Any input is appreciated. Thanks. Diana Hi Diana, You may consider the InterOrg Replication Utility to accomplish this. Read more about it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=E7A951D7-155...

Copying contacts?
Hi I am trying to copy my Contacts to an other PC using flash memory. For some reason no success? I seam to copy a lot extra on top of Contacts and therefore cannot proceede to the end of the .pst import wizard. Are there instructions to do that? Thanks Kalevi Yes. Never export or import. Copy the Contacts into a PST file. Open that PST file in the other installation. -- Russ Valentine [MVP-Outlook] "Kalevi" <kale@woodworld.fi> wrote in message news:dpakkj$aru$1@nyytiset.pp.htv.fi... > Hi > I am trying to copy my Contacts to an other PC using flash memory. For &...

Re: Function to copy data from a variable range?
I have a sheet with about 20,000 lines of data. From that sheet I am looking to group the data based on a parameter, into multiple different sheets. ie: If the category is "1" copy all that lines data into sheet A, If the category is "2", copy all the lines into sheet B. The problem is, the number of lines for each acatgeory type is not always the same. Some categories may only have 1 or two lines, other may have 200-300. Is there any type of function for this? Thanks! Doable but why not just use data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesA...

Copy lines from sheet 2 to sheet 6
I have some data in sheet 2 in col I. the data I want to capture is spaced by 7 spaces all the ay down: EX: I have data in sheet 2 col I row 1 ,7,14,21 etc I would like to show the data in Sheet 2 to Sheet 6 col B row 1,2,3,4, etc thanks On Sheet6, enter this in B1: =Sheet2!I1 And in B2 enter this: =INDEX(Sheet2!I:I,7*ROWS($1:1)) and copy down as needed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------------------------------------------------------...

Multiple accounts #3
I have 2 Hotmail and 1 MSN accounts set up in Outlook 2002. It shows that it sends and receives all 3 accounts successfully, but when I go to the Inbox of the Hotmail accounts, there aren't any messages. Thanks, Rick It says it send and received fine, but problems occur when you set up more than one HTTP account, so it is recommended that you only use one per profile ...

EzPaste-xl2ppt Excel add-in, automating the copy/paste of charts and data
Hello, This is to notify about the release of EzPaste (http:// www.EzPaste.net), an Excel add-in intended to completely automate the transfer of charts and data from Excel to PowerPoint. EzPaste identifies automatically all the charts and the tables defined in the active workbook. The user then selects which of them he wants to paste to PowerPoint=AE , and EzPaste does all the work, would you have to paste one or one hundred tables/charts at once Even if you have to paste the selected range or chart, EzPaste does it with the click of a button Sorry if this message is a bit advertising, b...

Using formulas to filter
Is there a way to filter a list by formulas the same way the Data->Filter option works? I'd like to take the matrix: Col A Col B Col C Col D Col E ===== ===== ===== ===== ===== Smith 30 $104.2 Yes 52 Jones 31 $155.3 No 51 Jones 31 $422.2 Yes 49 Freer 31 $424.3 Yes 42 Waylan 30 $322.5 No 50 Smith 31 $288.3 Yes 49 etc. And, using a formula, filter on Col B = 31 to produce: Col AA Col AB ...

What could keep formulas from recalculating?
I have a workbook that I received from a company with which I do business. On it are several worksheets (tabs). On one sheet, if I go to enter a formula in a cell, it displays it as text instead of calculating a value. For example, if I type =A1+A2 it will display that exact text in the cell as opposed to displaying the sum of cells A1 and A2. On other worksheets in the same workbook, entering formulas seems to work just fine. I have checked in Options, and auto-calc is checked (pressing F9 also has no effect). I can only assume that there is some setting of which I am unaware that is prev...

Using two conditions in a formula
How do I write the following formula: I am in cell I6 If g6 AND h6 is blank, then blank, else I5 minus g6 plus h -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 Richard, In cell I6, enter the formula: =IF(AND(G6="",H6=""),"",I5-G6-H6) Though you don't check for case when G6 is blank and H6 isn't, etc. HTH, Bernie MS Excel MVP "Richard ...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

vlookup help #3
hello, I cant figure out the proper syntax for the attached workbook. Th first 8 options on worksheet two work perfectly ( ie when checked, the appear on worksheet 3) I dont know how to apply the formula of th first 8 options to the rest of the checkboxes. Can someone please help me figure out how to do this? Thanks i advance. Aladdi Attachment filename: start order2.zip Download attachment: http://www.excelforum.com/attachment.php?postid=46674 -- Message posted from http://www.ExcelForum.com Right-click on the sheet tab for the Options worksheet. Select View ...

Automated Copy Paste
I have a template with 7 sheets that I continuously update with new data that I get from a seprate file. Each sheet is named "sheet1", "sheet2" "sheet3" and so on. My template has a series of summary sheets based off of the data that lies in these 7 inputs sheets. Currently, I use copy, paste special to update sheets 1-7 in my template. Instead of using copy, paste special one at a time, I would like to the move 7 sheets from my other file in my template. If I do this, the sheets will show up as duplicates so I will have "sheet1 (2)"...

Macro Copy and Paste
I am currently trying to copy from one workbook that has 7 differen tabs and paste into one worksheet on a separate master workbook. M problem is that when I set up the macro to copy from one of the tabs i the workbook and paste to the other work book, I only want the the row to come in where there is data. Currently I am having to run a macr that takes every 100 lines, but ideally I only want the informatio copy and pasted where there is only data. In addition, once I have copy and pasted everything from the first tab my next step is to copy and paste information from the second tab t th...

Login problem to CRM 3.0
Hi, I installed CRM 3.0 Professional Edition without any error. I can login perfectly from the CRM server either using http://localhost:5555/ or http://appserver:5555/ under the crmadmin user. If I login to an XP workstation with the same user (crmadmin) and specify http://appserver:5555/ in IE6, I got a login window - where I enter the correct DOMAINNAME\crmadmin username and password - and got the following message: "You are not authorized to view this page"..."HTTP Error 401.1 - Unauthorized... I think the prolem is around IIS security, but what shoud be the next step. Th...

Invest $3.70 & Earn $ 1 million By E-gold
Invest $3.70 and earn $1,000,000.00 By e-gold! Free sign-up. Guaranteed earnings! http://www.ptsu.biz/index.php?ref=petertian ...

I cannot paste from one workbook to another. Copy works, paste do.
When I attempt to copy from one workbook and paste into another, copy appears to work but paste does not. The paste menu item is grayed out in the workbook to which I attempt to paste. Are there setting that can be changed to aloow the paste?? Check to see if either workbook and/or worksheet is protected. If so, unprotect. Then Copy/Paste should work. BTW, how are you copy?paste(ing) Tab or content? Dennis "JimmyMc" wrote: > When I attempt to copy from one workbook and paste into another, copy appears > to work but paste does not. The paste menu item is grayed out...

how do I prevent a worksheet from being copied in excel
yet allow users to select values in a defined range Good evening kensanjose You really need to supply more information about what you mean - cop the file/sheets/ranges etc Dominic -- dominic ----------------------------------------------------------------------- dominicb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1893 View this thread: http://www.excelforum.com/showthread.php?threadid=38531 How do I prevent a worksheet from being copied in excel? Can I disable right click within a file so that the sheet cannot be copied into another file and manipula...

R-squared formula
I was just wondering if anybody knows the formula for the r-squared value for a power curve. Excel states that it uses a transformed r-squared value. I am trying to calculate the value by doing the mathematical calculations in the spreadsheet. I have gotten the correct equation that the chart shows, but I can't seem to get the correct r-squared value. The formulas that they give are: R^2 = 1-(SSE/SST) SSE = E(Yi-Yi^)^2 SST = (EYi^2)-(EYi)^2/n E is the best I can get to a sigma in this. Sigma is the sign to sum up all the indicated values. Yi is the original Y values. Yi^ ...