| |
これ、どちらが正解なのかな？
> ElseIf Cells(1, 1) > x Then
> y = 2
> GoTo st2
ElseIf Cells(1, 1) > x Then
y = 1
GoTo st2
もし、y = 1 が正しいなら前のコードの以下の部分を修正して下さい
If IsError(vntResult(i, 1)) Then
vntResult(i, 1) = 2
Else
を
If IsError(vntResult(i, 1)) Then
vntResult(i, 1) = 1
Else
にして下さい
また、質問のコードに近い形でコードを書くといかの様に成ります
比較元（B列）、比較先（A列）を配列に取り込み、結果も配列に書き出して
シートに出力しています
Public Sub Test4()
Dim i As Long
Dim j As Long
Dim vntScope As Variant
Dim vntKeys As Variant
Dim vntResult As Variant
vntScope = Range(Cells(1, "A"), _
Cells(65536, "A").End(xlUp)).Value
vntKeys = Range(Cells(1, "B"), _
Cells(65536, "B").End(xlUp)).Value
ReDim vntResult(1 To UBound(vntKeys, 1), 1 To 1)
For i = 1 To UBound(vntKeys, 1)
For j = 1 To UBound(vntScope, 1) - 1
If vntScope(j, 1) < vntKeys(i, 1) Then
If vntKeys(i, 1) <= vntScope(j + 1, 1) Then
vntResult(i, 1) = j + 1
Exit For
End If
End If
Next j
If vntResult(i, 1) = "" Then
If vntScope(1, 1) >= vntKeys(i, 1) Then
vntResult(i, 1) = 1
' vntResult(i, 1) = 2 'どちらが正解？
Else
vntResult(i, 1) = UBound(vntScope, 1)
End If
End If
Next i
Cells(1, "C").Resize(UBound(vntKeys, 1)).Value = vntResult
End Sub
|
|
- 04/2/20(金) 7:49 -
