|
楼主 |
发表于 2011-12-12 17:22
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub 公式()
- Application.Calculation = xlCalculationManual '手动重算
- With Sheets("Sheet2")
- a = .Range("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])+ROW()-2"
- .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
复制代码 |
|