スポンサードリンク | |
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 |
スポンサードリンク
PageViewCounter
Since2006/2/27