Copy formulas

S

sgrech

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.
 
S

swatsp0p

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
 
S

sgrech

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
 
D

Dave Peterson

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
 
S

sgrech

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
 
D

Dave Peterson

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.
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
Click to expand...
 
Top