エクセル練習問題:数量別単価表から該当する単価を求める(VBA)

Sub test1()
Dim i As Integer, j As Integer
Dim cn As Integer
Dim su(6) As String
Dim tanka As Double
'---数量区分
su(1) = "1-99"
su(2) = "100-199"
su(3) = "200-299"
su(4) = "300-499"
su(5) = "500-999"
su(6) = "1,000-1,500"

cn = 1
With Worksheets("単価表")

For i = 1 To 500
tanka = Int(Rnd() * (100 - 20 + 1) + 20) * 100

For j = 1 To 6
cn = cn + 1
'---品名の入力
.Cells(cn, 1) = "A" & Format(i, "000")
'---数量区分の入力
.Cells(cn, 2) = su(j)
'---単価の入力
.Cells(cn, 3) = tanka - Int(tanka * j * 5 / 100)
Next j
Next i
End With

End Sub

Sub test2()
Dim i As Integer, j As Integer
Dim cn As Integer
Dim kazu As Double

With Worksheets("数量表")
cn = 2
For i = 1 To 10
cn = cn + 1
kazu = Int(Rnd() * (1500 - 10 + 1) + 10)
.Cells(cn, 3) = kazu
kazu = Int(Rnd() * (500 - 1 + 1) + 1)
.Cells(cn, 2) = "A" & Format(kazu, "000")

Next i
End With

End Sub

Function ans(myR1 As Range, myR2 As Range) As Double
Dim i As Long
Dim myLot As Variant
Dim myFlag As Boolean

myFlag = False
With Worksheets("単価表")
For i = 2 To 3001
If .Cells(i, 1).Value = myR1.Value Then
myLot = Split(.Cells(i, 2).Value, "-")
If myR2.Value >= Val(Replace(myLot(0), ",", "")) And myR2.Value <= Val(Replace(myLot(1), ",", "")) Then
ans = .Cells(i, 3).Value
myFlag = True
Exit For
End If
End If
Next i
End With

If myFlag = False Then ans = -99999

Application.Volatile

End Function

Function keisan(myR1 As Range, myR2 As Range) As Double
Dim i As Long
Dim myLot As Variant
Dim myFlag As Boolean
Dim tanka As Variant

myFlag = False
With Worksheets("単価表")
tanka = .Range("A2:C3001").Value
End With

For i = LBound(tanka) To UBound(tanka)
If tanka(i, 1) = myR1.Value Then
myLot = Split(tanka(i, 2), "-")
If myR2.Value >= Val(Replace(myLot(0), ",", "")) And myR2.Value <= Val(Replace(myLot(1), ",", "")) Then
keisan = tanka(i, 3)
myFlag = True
Exit For
End If
End If
Next i

If myFlag = False Then keisan = -99999

Application.Volatile
End Function