Sub Test1()
Dim i As Long
Dim Range1 As Range
Dim Min1 As String
Dim rngU As Range
For i = 4 To 23
Set rngU = Range(Cells(i, 2), Cells(i, 13)) ' …?
'Set rngU = Range("B" & i & ":M" & i) ' …?
'Set rngU = Range("Bi:Mi") ' …?
MsgBox rngU.Address, , "DD 確認用 DD"
Min1 = WorksheetFunction.Min(rngU)
Set Range1 = rngU.Find(What:=Min1 _
, LookIn:=xlValues _
, LookAt:=xlWhole _
, MatchCase:=False)
If Not Range1 Is Nothing Then
Range1.Interior.color = vbRed
End If
Next i
End Sub
Dim i As Integer
Dim Range1 As Range
Dim Min1 As Double
Dim rngU As Range
For i = 4 To 23
Set rngU = Range(Cells(i, 2), Cells(i, 13))
MsgBox rngU.Address, , "DD 確認用 DD"
Min1 = WorksheetFunction.Small(rngU, 1)
MsgBox Min1
rngU.Select
Selection.Find(What:=Min1, After:=Cells(i, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Range1 Is Nothing Then
MsgBox "NG"
Else
MsgBox "OK"
Range1.Interior.ColorIndex = 1
End If
Next
End Sub
--実装例--
'1.同じ値が複数あっても前の値を返す(既存実装と同等)
Private Function myFind(Key As Single, Fields As Range) As Range
Dim CarCell As Range
For Each CarCell In Fields
If CarCell.Value = Key Then
Exit For
End If
Next
Set myFind = CarCell
End Function
---
'2.その行の検索条件にヒットするものは全て返す。
Private Function myFind(Key As Single, Fields As Range) As Collection
Dim CarCell As Range
Set myFind = New Collection
For Each CarCell In Fields
If CarCell.Value = Key Then
myFind.Add CarCell
End If
Next
End Function
---
2の場合戻り値がRangeでなくコレクションなんで上位でFor eachで回してやる必要ありますな。
Sub Test2()
Dim i As Long
Dim Min1 As Double
Dim MatchRe As Variant
Dim rngU As Range
Cells.Interior.ColorIndex = 0
For i = 4 To 23
Set rngU = Range(Cells(i, 2), Cells(i, 13))
Min1 = WorksheetFunction.Min(rngU)
MatchRe = Application.Match(Min1, rngU, 0)
If Not IsError(MatchRe) Then
rngU(MatchRe).Interior.Color = vbRed
End If
Next i
End Sub
例えば、B4:M4の最小値(MIN関数またはSMALL関数の戻り値)は
-2.27530864197531
でしたが、セル上の表示は、
-2.275308642
です。
これは、一般操作の「検索」でも-2.27530864197531ではヒットせず、
-2.275308642でヒットします。
ということは…
> Min1 = WorksheetFunction.Min(rngU)
の直後に
Min1 = WorksheetFunction.Round(Min1, 9)
と一発かませばいいはずです。
Sub Test1R()
Dim i As Long
Dim Range1 As Range
Dim Min1 As Double
Dim rngU As Range
Dim shtA As Worksheet
Set shtA = Worksheets("傾きの比較表")
For i = 4 To 23
Set rngU = shtA.Range(shtA.Cells(i, 2), shtA.Cells(i, 13))
rngU.Interior.ColorIndex = 0
Min1 = WorksheetFunction.Min(rngU)
Min1 = WorksheetFunction.Round(Min1, 9)
Debug.Print rngU.Address; Min1
Set Range1 = rngU.Find(What:=Min1 _
, LookIn:=xlValues _
, LookAt:=xlWhole _
, MatchCase:=False)
If Range1 Is Nothing Then
MsgBox "nothing"
Else
MsgBox Range1.Address, , "DD 確認用 DD"
Range1.Interior.Color = vbRed
End If
Next i
End Sub
ついでなので、MATCH関数の方も少々手直ししておきます。
MATCH関数の方は四捨五入をする必要がなくそのままの値で評価
するので、今回はコチラを使った方が適切かもしれません。
Sub Test2R()
Dim i As Long
Dim Min1 As Double
Dim MatchRe As Variant
Dim rngU As Range
Dim shtA As Worksheet
Set shtA = Worksheets("傾きの比較表")
For i = 4 To 23
Set rngU = shtA.Range(shtA.Cells(i, 2), shtA.Cells(i, 13))
rngU.Interior.ColorIndex = 0
Min1 = WorksheetFunction.Min(rngU)
MatchRe = Application.Match(Min1, rngU, 0)
If Not IsError(MatchRe) Then
rngU(MatchRe).Interior.Color = vbRed
End If
Next i
End Sub
Dim i As Long
Dim Range1 As Range
Dim Min1 As String
Dim rngU As Range
For i = 4 To 23
Set rngU = Range(Cells(i, 2), Cells(i, 13))
MsgBox rngU.Address, , "DD 確認用 DD"
Min1 = WorksheetFunction.Small(rngU, 1)
MsgBox Min1
rngU.Select
Selection.Find(What:=Min1, After:=Cells(i, 2), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Range1 Is Nothing Then
MsgBox "NG"
Else
MsgBox "OK"
Range1.Interior.ColorIndex = 1
End If
Next
End Sub
Sub TestMain()
Dim i As Long
Dim Rtn As Boolean
Dim Min1 As Double
Dim rngU As Range
Dim shtA As Worksheet
Set shtA = Worksheets("Sheet1")
For i = 1 To 4
Set rngU = shtA.Cells(i, "A").Resize(, 5)
rngU.Interior.ColorIndex = 0
Min1 = WorksheetFunction.Min(rngU)
Debug.Print rngU.Address; Min1; "TestMain"
'Rtn = SubFindVer1(rngU, Min1) ' … ?
'Rtn = SubFindVer2(rngU, Min1) ' … ?
'Rtn = SubFindVer3(rngU, Min1) ' … ?
'Rtn = SubMatchVer1(rngU, Min1) ' … ?
'Rtn = SubLoopVer1(rngU, Min1) ' … ?
If Not Rtn Then
MsgBox i & " 行目は該当なし。"
End If
Next i
End Sub
Function SubFindVer1(ByRef ArngU As Range _
, ByVal AMin As Double) As Boolean
Dim rngF As Range
AMin = WorksheetFunction.Round(AMin, 9) '誤りの原因A
' 値で検索する
Set rngF = ArngU.Find(What:=AMin _
, LookIn:=xlValues _
, LookAt:=xlWhole _
, MatchCase:=False)
If Not rngF Is Nothing Then
Debug.Print rngF.Address; rngF.Value; "SubFindVer1"
rngF.Interior.Color = vbRed
SubFindVer1 = True
End If
End Function
Function SubFindVer2(ByRef ArngU As Range _
, ByVal AMin As Double) As Boolean
Dim rngF As Range
Dim wKADRS1st As String
AMin = WorksheetFunction.Round(AMin, 9) '誤りの原因A
' 値で検索する
Set rngF = ArngU.Find(What:=AMin _
, LookIn:=xlValues _
, LookAt:=xlWhole _
, MatchCase:=False)
If Not rngF Is Nothing Then
wKADRS1st = rngF.Address
Do
Debug.Print rngF.Address; rngF.Value; "SubFindVer2"
rngF.Interior.Color = vbYellow
Set rngF = ArngU.FindNext(After:=rngF)
Loop Until wKADRS1st = rngF.Address
SubFindVer2 = True
End If
End Function
Function SubFindVer3(ByRef ArngU As Range _
, ByVal AMin As Double) As Boolean
Dim rngF As Range
' 数式で検索する 誤りの原因B
Set rngF = ArngU.Find(What:=AMin _
, LookIn:=xlFormulas _
, LookAt:=xlWhole _
, MatchCase:=False)
If Not rngF Is Nothing Then
Debug.Print rngF.Address; rngF.Value; "SubFindVer3"
rngF.Interior.Color = vbCyan
SubFindVer3 = True
End If
End Function
Function SubMatchVer1(ByRef ArngU As Range _
, ByVal AMin As Double) As Boolean
Dim MatchRe As Variant
Dim rngA As Range
MatchRe = Application.Match(AMin, ArngU, 0) '誤りの原因C
If Not IsError(MatchRe) Then
Set rngA = ArngU(MatchRe)
Debug.Print rngA.Address; rngA.Value; "SubMatchVer1"
rngA.Interior.Color = vbMagenta
SubMatchVer1 = True
End If
End Function
Function SubLoopVer1(ByRef ArngU As Range _
, ByVal AMin As Double) As Boolean
Dim rngA As Range
For Each rngA In ArngU
If rngA.Value = AMin Then
Debug.Print rngA.Address; rngA.Value; "SubLoopVer1"
rngA.Interior.Color = vbBlue
SubLoopVer1 = True
End If
Next rngA
End Function