如何用VBA的DO UNTIL...LOOP宏筛选25-30范围的数值

问题说明 #

原有VBA宏通过Do Until...Loop语句获取工作表中最后8个非空值，现需将判断条件从“非空”改为“数值介于25到30之间”。以下是原有代码及存在语法问题的修改思路，需修正实现逻辑。

原有代码 #

Cells(Application.Rows.Count, col_oleo).End(xlUp).Offset(0, 0).Select
oleo8 = ActiveCell.Value
oleo_hora8 = Cells(Application.Rows.Count, col_oleo).End(xlUp).Offset(0, col_oleo_hora).Value

Do Until oleo7 <> ""
    Selection.Offset(-1, 0).Select
    oleo7 = Selection.Value
    oleo_hora7 = Selection.Offset(0, col_oleo_hora).Value
Loop

Do Until oleo6 <> ""
    Selection.Offset(-1, 0).Select
    oleo6 = Selection.Value
    oleo_hora6 = Selection.Offset(0, col_oleo_hora).Value
Loop

存在语法错误的修改尝试 #

用户尝试调整判断条件，但不符合VBA语法规范：

Do Until oleo7 <30 and >25
    Selection.Offset(-1, 0).Select
    oleo7 = Selection.Value
    oleo_hora7 = Selection.Offset(0, col_oleo_hora).Value
Loop

Do Until oleo6 <30 and >25
    Selection.Offset(-1, 0).Select
    oleo6 = Selection.Value
    oleo_hora6 = Selection.Offset(0, col_oleo_hora).Value
loop

正确实现方案 #

1. 修正条件判断语法 #

VBA中判断数值范围需写出完整表达式，不能简写，同时要避免非数值单元格引发错误，完整条件应为：

' 数值严格介于25-30之间（若需包含边界，改为>=25和<=30）
IsNumeric(目标值) And 目标值 > 25 And 目标值 < 30

2. 优化后的单值获取代码（避免Select） #

原代码依赖Select和ActiveCell效率低且易出错，改用单元格变量直接引用：

Dim lastCell As Range
Set lastCell = Cells(Application.Rows.Count, col_oleo).End(xlUp)

' 获取第8个符合条件的值
oleo8 = lastCell.Value
oleo_hora8 = lastCell.Offset(0, col_oleo_hora).Value

' 获取第7个符合条件的值
Set lastCell = lastCell.Offset(-1, 0)
Do Until IsNumeric(lastCell.Value) And lastCell.Value > 25 And lastCell.Value < 30
    Set lastCell = lastCell.Offset(-1, 0)
    ' 防止遍历到表头或空行报错
    If lastCell.Row < 1 Then Exit Do
Loop
oleo7 = lastCell.Value
oleo_hora7 = lastCell.Offset(0, col_oleo_hora).Value

' 获取第6个符合条件的值
Set lastCell = lastCell.Offset(-1, 0)
Do Until IsNumeric(lastCell.Value) And lastCell.Value > 25 And lastCell.Value < 30
    Set lastCell = lastCell.Offset(-1, 0)
    If lastCell.Row < 1 Then Exit Do
Loop
oleo6 = lastCell.Value
oleo_hora6 = lastCell.Offset(0, col_oleo_hora).Value

3. 批量处理简化代码（获取全部8个值） #

重复写8次循环冗余，可用数组批量处理：

' 替换为实际列号
Dim colOleo As Long, colOleoHora As Long
colOleo = 2
colOleoHora = 1

Dim lastCell As Range, i As Integer
Dim oleoVals(1 To 8) As Variant, horaVals(1 To 8) As Variant

Set lastCell = Cells(Application.Rows.Count, colOleo).End(xlUp)

For i = 8 To 1 Step -1
    ' 找到当前符合条件的单元格
    Do While Not (IsNumeric(lastCell.Value) And lastCell.Value > 25 And lastCell.Value < 30)
        Set lastCell = lastCell.Offset(-1, 0)
        If lastCell.Row < 1 Then
            MsgBox "符合条件的数值不足8个"
            Exit Sub
        End If
    Loop
    oleoVals(i) = lastCell.Value
    horaVals(i) = lastCell.Offset(0, colOleoHora).Value
    Set lastCell = lastCell.Offset(-1, 0)
Next i

' 后续可直接使用oleoVals(1)至oleoVals(8)、horaVals(1)至horaVals(8)

关键注意点 #

• 条件必须完整：不能写oleo7 <30 and >25，需明确写为oleo7 >25 And oleo7 <30
• 加入IsNumeric判断：避免单元格为文本时触发类型错误
• 避免使用Select：直接操作单元格对象更高效稳定
• 添加边界判断：防止循环遍历到工作表第一行以上导致报错

内容的提问来源于stack exchange，提问作者pedro_pedro