EXCEL VBA 公式導入

Sub 授信()


Dim x1, y1, y2, y3

Dim x2, y4



x1 = InputBox("請輸入AI330月份")



y1 = "=ROUND(VLOOKUP(A6,'[AI330-" & x1 & ".xls]Table1'!$A:$F,4,0)/1000,0)"

y2 = "=ROUND(VLOOKUP(A6,'[AI330-" & x1 & ".xls]Table1'!$A:$F,5,0)/1000,0)"

y3 = "=ROUND(VLOOKUP(A6,'[AI330-" & x1 & ".xls]Table1'!$A:$F,6,0)/1000,0)"



x2 = InputBox("請輸入放款結構月份")

y4 = "=ROUND(VLOOKUP('[" & x2 & "放款結構.xls]" & x2 & "結構'!R[26]C1,'[" & x2 & "放款結構.xls]" & x2 & "結構'!C1:C16,5,0)/1000,0)"





Range("J6").Value = y1

Range("J6:J18").Select

Selection.FillDown

Range("J19").Value = "=SUM(J6:J18)"





Range("k6").Value = y2

Range("k6:k18").Select

Selection.FillDown

Range("k19").Value = "=SUM(k6:k18)"



Range("l6").Value = y3

Range("l6:l18").Select

Selection.FillDown

Range("l19").Value = "=SUM(l6:l18)"



Range("J6:L18").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



Range("J6").Select



合計





Range("H9").Select

ActiveCell.FormulaR1C1 = y4

Range("H9").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



Range("J6").Select



End Sub



Sub 合計()





Range("F6").Value = "=SUM(C6:E6)"

Range("F6").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.FillDown





Range("I6").Value = "=SUM(F6:H6)"

Range("I6").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.FillDown



End Sub