是讓使用者自行輸入參數，
([B1][B2][D1][D2][F2]這幾欄)
其餘欄位會自動產生，
但目前執行上有個問題，
當[A11]大於預設值[D2]
如下圖
要如何讓[A11]大於[D2]時等於[D2]，
需要修改的地方是藍色字體的部份...
VBA巨集
Sub P_IR3()
Dim i, j, k, n As Integer
Dim amt As Long
Dim h, l, x As Single
Dim ss, ran1, ran2, ran3, r, c As String
Dim rg1 As Range
ss = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
[A1] = "存款金額"
[C1] = "存款年數"
[A2] = "最低利率"
[C2] = "最高利率"
[E2] = "利率級距"
Set rg1 = Range("B1:D1")
With rg1.Interior.Color = RGB(204, 204, 255)
End With
n = [D1]
l = [B2]
h = [D2]
x = [F2]
amt = [B1]
Range("A3:AZ60").Clear
[A3] = " 年數"+ Chr(10) +" 年利率"
Range("A3").Select
With Selection.Borders(xlDiagonalDown)
.LineStyle = xlContinuous
End With
k = CInt((h - l) / x)
For i = 0 To k
Cells(i + 4, 1) = l + x * i
Next
'ran1 = "A4:A" + CStr(4 + k)
'Range(ran1).Select
r = "A" & 4
c = "A" & (k + 4)
Range(r, c).Select
Selection.Style = "Percent"
Selection.NumberFormatLocal = "0.000%"
ran3 = "B2,D2"
Range(ran3).Select
Selection.Style = "Percent"
Selection.NumberFormatLocal = "0.0%"
Selection.Font.Size = 14
Selection.Font.Color = RGB(0, 0, 255)
If n + 1 <= 26 Then
s1 = Mid(ss, n + 1, 1)
Else
s1 = "A" + Mid(ss, n + 1 - 26, 1)
End If
Range("B:" + s1).Select
Selection.ColumnWidth = 9.5
ran2 = "B4:" + s1 + CStr(4 + k)
Range(ran2).Select
Selection.NumberFormatLocal = "#,##0_ "
For i = 4 To k + 4
For j = 2 To n + 1
Cells(3, j) = j - 1
Cells(i, j) = amt * (1 + Cells(i, 1)) ^ Cells(3, j)
Next
Next
End Sub
Private Sub CommandButton1_Click()
P_IR3
End Sub
