For i = 10 To [f65000].End(3).Row '循环f的所有行
If Cells(i, "f") <> 0 Then 'f列为零的过滤掉
s1 = 0 's1和s2是两个存储b列值的变量,初始化为零
s2 = 0
js = 0 '计数器
For k = i To 10 Step -1 '对b列进行倒循环,从i行到第10行,变量k初始值k=i
If js = 2 Then '只记录两个数,计数器=2退出循环
Exit For
End If
If (Cells(k, 2) <> 0) Then '只对b列不为零的进行处理
If js = 0 Then '计数器=0,计录第一个数字
s1 = Cells(k, 2)
js = js + 1
Else '计数器<>0表示是记录第二个数,计数器最大也就是1
s2 = Cells(k, 2)
js = js + 1
End If
End If
Next k
If (s1 <> 0) And (s2 <> 0) Then '两个变量都有数字
If s1 > s2 Then '判断大小
Cells(i, "g") = 1 '在i行的g列写入
Else
Cells(i, "g") = -1 '写入另一种判断结果
End If
Else
if s1<>0 then '要防止b列所有数字都是零,都是零就不写入任何数
Cells(i, "g") = 1 '只有一个数字的情况,第二个数字是零
endif
End If
End If
Next i
Sub test()
Dim i&, arr, j&, m&, rowmax, a, b, s, brr, k, s1, s2
rowmax = Sheets(1).UsedRange.Rows.Count
ReDim brr(1 To rowmax - 9, 1 To 1)
a = [b10].Resize(rowmax - 9).Address
b = [f10].Resize(rowmax - 9).Address
s = "choose({1,2}," & a & "," & b & ")"
arr = Evaluate(s)
For i = UBound(arr) To 1 Step -1
k = 0: s1 = 0: s2 = 0
If arr(i, 2) <> 0 Then
For j = i To 1 Step -1
If k = 1 Then Exit For
If arr(j, 1) <> 0 Then
s1 = arr(j, 1)
For m = j - 1 To 1 Step -1
If j - 1 = 1 Then brr(i, 1) = 1: Exit For
If arr(m, 1) <> 0 Then
If k = 1 Then Exit For
s2 = arr(m, 1): k = 1
If s2 > s1 Then
brr(i, 1) = -1
Else
brr(i, 1) = 1
End If
End If
Next
End If
Next
End If
Next
Sheet1.Range("g10").Resize(rowmax - 9, 1) = brr
End Sub