Excel 2010 Issue - Formula not working..

politot

New Member
Joined
Nov 22, 2012
Messages
22
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
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*-1D4F14G16

<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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,619
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top