| |
速いかどうかは分かりませんが、分かりやすいコードとして・・
Sub Test()
Dim FR As Range, FR2 As Range, MyR As Range
Dim i As Long, Mx As Long, Mi As Long
x = UserForm1.ComboBox1.Value
With Sheets("Sheet1")
Set FR = .Columns(1).Find(x, , xlValues, xlWhole)
Set FR2 = .Columns(1).Find(x, , xlValues, , xlPrevious)
Set MyR = .Range(FR, FR2).Offset(, 1)
End With
Mx = WorksheetFunction.Max(MyR)
Mi = WorksheetFunction.Min(MyR)
If Mx - (Mi - 1) = MyR.Cells.Count Then
MsgBox "未使用の数値がありません", 64
Else
For i = Mi + 1 To Mx
If IsError(Application.Match(i, MyR, 0)) Then
Exit For
End If
Next i
UserForm1.ComboBox2.AddItem i
End If
Set FR = Nothing: Set FR2 = Nothing: Set MyR = Nothing
End Sub
というのは、どうでしょーか ? 未使用の最小値が1つ入ります。
|
|
