excelvlog办公技巧(VBA 自动填充EXCEL函数公式)

实际工作中能用EXCEL基本功能或者函数公式的尽量不用VBA代码,因为VBA代码在不同的机器需要设置才能使用,公司文员一般都是小白,别说VBA,连函数也没几个会用的.另一个就是表格数据经常要放到手机、平板等随时查看与操作,这时VBA就比较尴尬了.

我一般给同事的表格是不带VBA代码的,自己用有时候又会写些代码上去.经常函数公式和VBA代码混着用,反正怎么方便怎么来吧.能有效地完成工作就好.这样就经常用到VBA自动填充EXCEL函数公式:

Sub 入库() Application.ScreenUpdating = False ActiveSheet.Range("$A$1:$F$500").AutoFilter Field:=3, Criteria1:="<>" Range("A3:F500").Copy ActiveSheet.Range("$A$1:$F$500").AutoFilter Worksheets("成品仓卷材库存表").Activate Range("A4") = "=B4&D4" Range("H4") = "=IFERROR(LEFT(VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),6,0),FIND(""-"",VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),6,0))-1),"""")" Range("I4") = "=IFERROR(LEFT(VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),7,0),FIND(""."",VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),7,0),1)-1),"""")" Range("J4") = "=IFERROR(RIGHT(VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),6,0),LEN(VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),6,0))-FIND(""-"",VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),6,0),1)),"""")" Range("K4") = "=IFERROR(RIGHT(VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),7,0),LEN(VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),7,0))-FIND(""."",VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),7,0),1)),"""")" Range("L4") = "=IFERROR(TEXT(VLOOKUP(B4,INDIRECT(""生产订单列表!$A$1:$O$5000""),9,0),""yyyy-mm-dd""),"""")" Range("Q4") = "=IF(AND(COUNTIF(成品出库记录表!$A:$A,B4&D4)>0,D4<>""""),""已发货或者卷号重复"","""")" Range("R4") = "=B4&C4" Range("B" & Range("B65535").End(xlUp).Offset(1, 0).Row).PasteSpecial xlPasteValues Range("A4").AutoFill Range("A4:A" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("H4").AutoFill Range("H4:H" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("I4").AutoFill Range("I4:I" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("J4").AutoFill Range("J4:J" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("K4").AutoFill Range("K4:K" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("L4").AutoFill Range("L4:L" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("Q4").AutoFill Range("Q4:Q" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("R4").AutoFill Range("R4:R" & Cells(Rows.Count, "B").End(xlUp).Row), xlFillValues Range("N" & Range("N4").End(xlDown).Offset(1, 0).Row & ":N" & Range("B4").End(xlDown).Row).Value = "L" Range("O" & Range("O4").End(xlDown).Offset(1, 0).Row & ":O" & Range("B4").End(xlDown).Row).Value = Application.WorksheetFunction.Text(Format(Date, "yyyy-mm-dd"), "yyyy-mm-dd") Worksheets("生产单台账模版").Activate Range("C3:C500,G3:H500").ClearContents

Application.ScreenUpdating = True End Sub