|
楼主 |
发表于 2011-12-12 10:42
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 LangQueS 于 2011-12-12 11:54 编辑
lindw 发表于 2011-12-12 08:48
附件我重新上传,sheet2和sheet3有公式的行数在30000行左右
sheet2的公式如下,是在2003中做的,请在2007中测试一下:
Sub 表2公式()
Application.Calculation = xlCalculationManual '手动重算
With Sheets("sheet2")
a = [d65536].End(xlUp).Row
If a > 1 Then
.Range("A2:A" & a).FormulaR1C1 = "=IF(RC[3]="""","""",VLOOKUP(RC[3],数据录入!C:C[3],4,))"
.Range("B2:B" & a).FormulaR1C1 = _
"=IF(RC[2]="""","""",VLOOKUP(RC[2],数据录入!C[-1]:C[1],3,))"
.Range("C2:C" & a).FormulaR1C1 = _
"=IF(RC[1]="""","""",VLOOKUP(RC[1],数据录入!C[-2]:C[12],15,))"
.Range("A2:C" & a) = .Range("A2:C" & a).Value
.Range("E2:E" & a).FormulaR1C1 = _
"=IF(RC[-1]="""","""",VLOOKUP(RC[-1],数据录入!C[-4]:C[11],16,))"
.Range("E2:E" & a) = .Range("E2:E" & a).Value
.Range("H2:H" & a).FormulaR1C1 = _
"=IF(RC[-4]="""","""",IF(SUMIF(数据录入!C[-7],Sheet2!RC[-4],数据录入!C[2])>=0,1,-1))"
.Range("I2:I" & a).FormulaR1C1 = "=IF(RC[-5]="""","""",1)"
.Range("J2:J" & a).FormulaR1C1 = "=IF(RC[-6]="""","""",数据录入!R2C[13])"
.Range("K2:K" & a).FormulaR1C1 = _
"=IF(RC[-7]="""","""",VLOOKUP(RC[-7],数据录入!C[-10]:C[-6],5,))"
.Range("L2:L" & a).FormulaR1C1 = _
"=IF(RC[-8]="""","""",VLOOKUP(RC[-8],数据录入!C[-11]:C[-6],6,))"
.Range("M2:M" & a).FormulaR1C1 = _
"=IF(RC[-9]="""","""",VLOOKUP(RC[-9],数据录入!C[-12]:C[4],17,))"
.Range("N2:N" & a).FormulaR1C1 = _
"=IF(RC[-10]="""","""",VLOOKUP(RC[-10],数据录入!C[-13]:C[4],18))"
.Range("O2:O" & a).FormulaR1C1 = _
"=IF(RC[-11]="""","""",VLOOKUP(RC[-11],数据录入!C[-14]:C[4],19,))"
.Range("P2:P" & a).FormulaR1C1 = _
"=IF(RC[-12]="""","""",VLOOKUP(RC[-12],数据录入!C[-15]:C[4],20,))"
.Range("Q2:Q" & a).FormulaR1C1 = _
"=IF(RC[-13]="""","""",IF(RC[1]=""赊购"",""P002"",""P001""))"
.Range("R2:R" & a).FormulaR1C1 = _
"=IF(RC[-14]="""","""",VLOOKUP(RC[-14],数据录入!C[-17]:C[3],20,))"
.Range("S2:S" & a).FormulaR1C1 = "=IF(RC[-15]="""","""",162)"
.Range("H2:S" & a) = .Range("H2:S" & a).Value
.Range("W2:W" & a).FormulaR1C1 = "=IF(RC[-19]="""","""",0)"
.Range("X2:X" & a).FormulaR1C1 = "=IF(RC[-20]="""","""",0)"
.Range("Y2:Y" & a).FormulaR1C1 = _
"=IF(RC[-21]="""","""",IF(VLOOKUP(RC[-21],数据录入!C[-24]:C[-23],2,)=0,"""",VLOOKUP(RC[-21],数据录入!C[-24]:C[-23],2,)))"
.Range("w2:y" & a) = .Range("w2:y" & a).Value
End If
End With
Application.Calculation = xlCalculationAutomatic '自动重算
End Sub
|
|