Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copy formulas
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...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=469295 |
#2
|
|||
|
|||
If your data is in column A and your formula is in column B, simply double click on the small square in the lower right corner of the cell pointer. This will auto fill your formula down only as far as there is data in column A HTH Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=469295 |
#3
|
|||
|
|||
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 for me. Any further suggestions?? Thanks Simon -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=469295 |
#4
|
|||
|
|||
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...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=469295 |
#5
|
|||
|
|||
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...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=469295 -- Dave Peterson |
#6
|
|||
|
|||
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)="f ix")),(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...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=469295 |
#7
|
|||
|
|||
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)="f ix")),(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...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=469295 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to copy only the layout and formulas and not the exisiting | Excel Discussion (Misc queries) | |||
How to copy formulas from one workbook to another | Excel Discussion (Misc queries) | |||
How do I save a copy of an excel file without the formulas? | Excel Worksheet Functions | |||
Copy formulas - strange things happens | Excel Discussion (Misc queries) | |||
how do i copy vertical formulas to horizonal and keep same cell r. | Excel Discussion (Misc queries) |