Hi Excel Guru's,
Can someone help me how this excel formula will generate a result using excel 2010, this code works perfect on excel 2007 but i need this formula works on 2010. Many Thanks!
Sheet 9
<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
Sheet 11
<colgroup><col width="74"><col span="2" width="94"><col width="91"><col width="39"><col width="33"><col width="66"><col width="96"><col width="101"><col width="104"><col width="89"><col span="2" width="64"><col width="99"><col width="64"></colgroup><tbody>
</tbody>
Sheet8.Select
Sheet8.[a1].Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
w = ActiveCell.Row
With Sheet9
.Range(.Cells(3, 1), .Cells(w, 1)).Formula = "=" & Sheet9.Range("A1").Value
.Range(.Cells(3, 2), .Cells(w, 2)).Formula = "=" & Sheet9.Range("B1").Value
.Range(.Cells(3, 3), .Cells(w, 3)).Formula = "=" & Sheet9.Range("C1").Value
.Range(.Cells(3, 4), .Cells(w, 4)).Formula = "=" & Sheet9.Range("D1").Value
.Range(.Cells(3, 5), .Cells(w, 5)).Formula = "=" & Sheet9.Range("E1").Value
.Range(.Cells(3, 6), .Cells(w, 6)).Formula = "=" & Sheet9.Range("F1").Value
.Range(.Cells(3, 7), .Cells(w, 7)).Formula = "=" & Sheet9.Range("G1").Value
.Range(.Cells(16, 10), .Cells(w, 10)).Formula = "=" & Sheet9.Range("J1").Value
.Range(.Cells(16, 11), .Cells(w, 11)).Formula = "=" & Sheet9.Range("K1").Value
.Range(.Cells(16, 12), .Cells(w, 12)).Formula = "=" & Sheet9.Range("L1").Value
.Range(.Cells(16, 13), .Cells(w, 13)).Formula = "=" & Sheet9.Range("M1").Value
End With
With Sheet3
.Range(.Cells(16, 1), .Cells(w, 1)).Formula = "=" & Sheet3.Range("a1").Value
.Range(.Cells(16, 2), .Cells(w, 2)).Formula = "=" & Sheet3.Range("b1").Value
.Range(.Cells(16, 3), .Cells(w, 3)).Formula = "=" & Sheet3.Range("c1").Value
.Range(.Cells(16, 4), .Cells(w, 4)).Formula = "=" & Sheet3.Range("d1").Value
End With
Sheets("Calc").Select
With ActiveSheet
.Range(.Cells(2, 1), .Cells(w, 4)).AutoFilter Field:=3, Criteria1:=RGB(194 _
, 214, 154), Operator:=xlFilterCellColor
End With
Sheet3.Range("a2:d2").Select
Sheet3.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("PFcalc").Select
Sheet10.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Generating number of rows
Sheet10.[a1].Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
x = ActiveCell.Row
With Sheet11
.Range(.Cells(4, 1), .Cells(x, 1)).Formula = "=" & Sheet11.Range("A1").Value
.Range(.Cells(3, 2), .Cells(x, 2)).Formula = "=" & Sheet11.Range("B1").Value
.Range(.Cells(3, 3), .Cells(x, 3)).Formula = "=" & Sheet11.Range("C1").Value
.Range(.Cells(3, 4), .Cells(x, 4)).Formula = "=" & Sheet11.Range("D1").Value
.Range(.Cells(3, 7), .Cells(x, 7)).Formula = "=" & Sheet11.Range("G1").Value
.Range(.Cells(3, 8), .Cells(x, 8)).Formula = "=" & Sheet11.Range("H1").Value
.Range(.Cells(3, 9), .Cells(x, 9)).Formula = "=" & Sheet11.Range("I1").Value
.Range(.Cells(3, 10), .Cells(x, 10)).Formula = "=" & Sheet11.Range("J1").Value
.Range(.Cells(3, 11), .Cells(x, 11)).Formula = "=" & Sheet11.Range("K1").Value
.Range(.Cells(3, 12), .Cells(x, 12)).Formula = "=" & Sheet11.Range("L1").Value
End With
Can someone help me how this excel formula will generate a result using excel 2010, this code works perfect on excel 2007 but i need this formula works on 2010. Many Thanks!
Sheet 9
IF(LEFT(Data!A3,10)="Account ov", SEARCH(":",Data!A3,1),"") | IF(A3<>"", MID(Data!A3,A3+3,20), 0) | IF(LEFT(Data!A3, 7)="Renewal", SEARCH(":",Data!A3),"") | IF(c3<>"", MID(Data!A3,c3+2, 15),"") | IF(LEFT(Data!A3,7)="Date is",SEARCH(":",Data!A3),"") | IF(e3<>"",MID(Data!A3,e3+2,15),"") | IF(LEFT(Data!A3,9)="Date Act",LEFT(Data!A4,7),"") | B3*-1 | D4 | F14 | G16 |
<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>
Sheet 11
IF(D4<>"", IF(PFcalc!B4<>"",IF(PFcalc!A4=0,A3,PFcalc!A4),PFcalc!A4),"") | IF(PFcalc!C3<>"",PFcalc!B3,"") | IF(PFcalc!C3<>"",PFcalc!C3,"") | IF(PFcalc!C3<>"",PFcalc!D3,"") | IF(OR(A3<>A2,A3<>A4,AND(A3<>"",A4="")),A3,"") | IF(OR(AND($A3<>"",$A4=""),$A3<>$A4),B3, "") | IF(OR(AND($A3<>"",$A4=""),$A3<>$A4),C3, "") | IF(A4<>"", IF($A3<>$A4,C4,""),"") | IF(A4<>"", IF($A3<>$A4,D4,""),"") | IF(AND($O$2="no", I3<>"",A3<>"",K3=""), I3, IF(AND($O$2="Yes", A4="",A3<>""), "OPEN", IF(G3<>"", IF(AND(H3>=I3,H3<=J3),H3,K3),""))) |
<colgroup><col width="74"><col span="2" width="94"><col width="91"><col width="39"><col width="33"><col width="66"><col width="96"><col width="101"><col width="104"><col width="89"><col span="2" width="64"><col width="99"><col width="64"></colgroup><tbody>
</tbody>
Sheet8.Select
Sheet8.[a1].Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
w = ActiveCell.Row
With Sheet9
.Range(.Cells(3, 1), .Cells(w, 1)).Formula = "=" & Sheet9.Range("A1").Value
.Range(.Cells(3, 2), .Cells(w, 2)).Formula = "=" & Sheet9.Range("B1").Value
.Range(.Cells(3, 3), .Cells(w, 3)).Formula = "=" & Sheet9.Range("C1").Value
.Range(.Cells(3, 4), .Cells(w, 4)).Formula = "=" & Sheet9.Range("D1").Value
.Range(.Cells(3, 5), .Cells(w, 5)).Formula = "=" & Sheet9.Range("E1").Value
.Range(.Cells(3, 6), .Cells(w, 6)).Formula = "=" & Sheet9.Range("F1").Value
.Range(.Cells(3, 7), .Cells(w, 7)).Formula = "=" & Sheet9.Range("G1").Value
.Range(.Cells(16, 10), .Cells(w, 10)).Formula = "=" & Sheet9.Range("J1").Value
.Range(.Cells(16, 11), .Cells(w, 11)).Formula = "=" & Sheet9.Range("K1").Value
.Range(.Cells(16, 12), .Cells(w, 12)).Formula = "=" & Sheet9.Range("L1").Value
.Range(.Cells(16, 13), .Cells(w, 13)).Formula = "=" & Sheet9.Range("M1").Value
End With
With Sheet3
.Range(.Cells(16, 1), .Cells(w, 1)).Formula = "=" & Sheet3.Range("a1").Value
.Range(.Cells(16, 2), .Cells(w, 2)).Formula = "=" & Sheet3.Range("b1").Value
.Range(.Cells(16, 3), .Cells(w, 3)).Formula = "=" & Sheet3.Range("c1").Value
.Range(.Cells(16, 4), .Cells(w, 4)).Formula = "=" & Sheet3.Range("d1").Value
End With
Sheets("Calc").Select
With ActiveSheet
.Range(.Cells(2, 1), .Cells(w, 4)).AutoFilter Field:=3, Criteria1:=RGB(194 _
, 214, 154), Operator:=xlFilterCellColor
End With
Sheet3.Range("a2:d2").Select
Sheet3.Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("PFcalc").Select
Sheet10.Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Generating number of rows
Sheet10.[a1].Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
x = ActiveCell.Row
With Sheet11
.Range(.Cells(4, 1), .Cells(x, 1)).Formula = "=" & Sheet11.Range("A1").Value
.Range(.Cells(3, 2), .Cells(x, 2)).Formula = "=" & Sheet11.Range("B1").Value
.Range(.Cells(3, 3), .Cells(x, 3)).Formula = "=" & Sheet11.Range("C1").Value
.Range(.Cells(3, 4), .Cells(x, 4)).Formula = "=" & Sheet11.Range("D1").Value
.Range(.Cells(3, 7), .Cells(x, 7)).Formula = "=" & Sheet11.Range("G1").Value
.Range(.Cells(3, 8), .Cells(x, 8)).Formula = "=" & Sheet11.Range("H1").Value
.Range(.Cells(3, 9), .Cells(x, 9)).Formula = "=" & Sheet11.Range("I1").Value
.Range(.Cells(3, 10), .Cells(x, 10)).Formula = "=" & Sheet11.Range("J1").Value
.Range(.Cells(3, 11), .Cells(x, 11)).Formula = "=" & Sheet11.Range("K1").Value
.Range(.Cells(3, 12), .Cells(x, 12)).Formula = "=" & Sheet11.Range("L1").Value
End With