VBA for循环；如果单元格在范围内，则插入范围值。

Question

我正努力使这件事成功。我有一个美元金额的清单，我需要循环它们，并确定哪些属于特定的范围。

当前数据

Name      Value     Range     
Bob       $1403
Julius    $11,425
Mike      $20,493
Lucas     $9,970

期望输出

Name     Value     Range    
Bob      $1403     <$10,000
Julius   $11,425   $10,000-$50,000
Mike     $20,493   $10,000-$50,000
Lucas    $9,970    <$10,000

电流结果

Name     Value     <$10,000    
Bob      $1403     <$10,000
Julius   $11,425  
Mike     $20,493   
Lucas    $9,970    

电流码

Sub FindMatchingValue()
 Dim assetRange As Range
 Set assetRange = Range("B2:B5")
 Set nextRange = Range("C2:C5")
 For Each c In Worksheets("Single Audit").Range("B2:B5").Cells
    If c(i, 1).Value <= 10000 Then
        nextRange(c).Value = "<$10,000"
   Else
   If (c(i, 1).Value >= 10000) And (c(i, 1).Value <= 50000) Then
        nextRange(c).Value = "$10,000-$50,000"
    End If
    End If
  Next c
End Sub

任何帮助都将不胜感激。谢谢!

Answer 1

有一个较短的版本：

Sub FindMatchingValue()
    With Worksheets("Single Audit").Range("C2:C5")
        .Formula = "=IF(B2<10000,""<$10,000"",IF(AND(B2>10000,B2<=50000),""$10,000-$50,000"",""""))"
        .Value = .Value
    End With
End Sub

上面的代码使用公式查找结果，.Value = .Value用code结果重写公式。

或者如果您想修改您的代码：

Sub FindMatchingValue2()
    Dim assetRange As Range
    Dim c As Range

    Set assetRange = Worksheets("Single Audit").Range("B2:B5")

    For Each c In assetRange
        If c.Value <= 10000 Then
            c.Offset(, 1).Value = "<$10,000"
        ElseIf (c.Value >= 10000) And (c.Value <= 50000) Then
            c.Offset(, 1).Value = "$10,000-$50,000"
        End If
    Next c
End Sub

Answer 2

为什么VBA要执行这么简单的任务？

假设值从B2开始，编写公式：

=LOOKUP(B2;{0;10000;50000.01};{"<$10,000";"$10,000-$50,000";">$50,000"})

然后复制下来

根据您的区域设置，您可能需要替换arg分隔符";“