PDA

View Full Version : Solved: How to design a code based on excel formula?



genracela
04-13-2010, 07:38 PM
I have a copy and paste code right now

example:

Sub CopyTo()

Workbooks("CN2.xls").Sheets("Sheet1").Range("J2:J9000").Copy
ThisWorkbook.Sheets("Total").Range("B949").PasteSpecial Paste:=xlPasteValues
Workbooks("CN2.xls").Sheets("Sheet1").Range("E2:E9000").Copy
ThisWorkbook.Sheets("Total").Range("C949").PasteSpecial Paste:=xlPasteValues
Workbooks("CN2.xls").Sheets("Sheet1").Range("A2:A9000").Copy
ThisWorkbook.Sheets("Total").Range("D949").PasteSpecial Paste:=xlPasteValues

End Sub


And based on that copied data, I would like to insert a VB code based on my excel formulas

examples:


E2 =IF(A2<140%,"WITHIN SLA","ABOVE SLA")
and
F2 =IF(G2=0,0,ROUND((AL2/G2),2))
and
=IF(F2>VLOOKUP(A2,'SLA Agreements'!$A:$C,MATCH(Total!U2,'SLA Agreements'!$A$2:$C$2,0),0)*G2,F2-VLOOKUP(A2,'SLA Agreements'!$A:$C,MATCH(Total!U2,'SLA Agreements'!$A$2:$C$2,0),0)*G2,0)

and I want to do that from row 2 all the way down

Thanks!

Bob Phillips
04-13-2010, 11:42 PM
All the way down where?

Where does that last formula go?

Do you want the formulas embedded in the worksheet, or the results?

genracela
04-13-2010, 11:52 PM
if formula in Column E2 is = IF(A2<140%,"WITHIN SLA","ABOVE SLA")
I want that formula to go all the way down to lets say E9000

if formula in Column F2 is =IF(G2=0,0,ROUND((AL2/G2),2))
I want that formula to go all the way down to F9000

if formula in G2 is
=IF(F2>VLOOKUP(A2,'SLA Agreements'!$A:$C,MATCH(Total!U2,'SLA Agreements'!$A$2:$C$2,0),0)*G2,F2-VLOOKUP(A2,'SLA Agreements'!$A:$C,MATCH(Total!U2,'SLA Agreements'!$A$2:$C$2,0),0)*G2
I want that formula to go all the way down to G9000

Then, I want the result to show in the cell not the formula.


Thanks!