如何适用于每一行?

Question

我有这个代码，但我想把它应用到所有4到750行。实际上我对vba了解不多。有人能帮我一下吗?

Sub test()


dinstance = Range("AO4").Value
Weight = Range("AN4").Value
Dim type_truck As String

If distance <= 30 And Weight <= 1200 Then
type_truck = "Large Van"
ElseIf distance > 30 And Weight > 1200 And Weight <= 7500 Then
type_truck = "Large Truck 10 - 20t"
ElseIf distance > 30 And Weigth > 7500 And Weight <= 13000 Then
type_truck = "Large Truck > 20t"
Else
type_truck = "LHV"
End If

Range("AP4").Value = type_truck

End Sub

Answer 1

与其使用sub来获取和设置range的值，我建议您编写自己的函数-它可以将距离和权重作为参数并返回值。

调用AP4中的函数和所有其他您想要的结果- https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/function-statement

e。g含量AP4为=test(AO4, AN4)

function test(byval distance as long, weight as long) as string


Dim type_truck As String

If distance <= 30 And Weight <= 1200 Then
type_truck = "Large Van"
ElseIf distance > 30 And Weight > 1200 And Weight <= 7500 Then
type_truck = "Large Truck 10 - 20t"
ElseIf distance > 30 And Weigth > 7500 And Weight <= 13000 Then
type_truck = "Large Truck > 20t"
Else
type_truck = "LHV"
End If

test = type_truck

End function

你可能也会发现它有助于阅读select case声明https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/select-case-statement

Answer 2

下面是一些可以为您工作的代码。它从第4行开始，将转到第100行(如果需要调整，请更新100行)。

-AcingExcel.com

Sub test()

For i = 4 To 100

distance = Sheet1.Cells(i, "AO")
Weight = Sheet1.Cells(i, "AN")

dinstance = Range("AO4").Value
Weight = Range("AN4").Value
Dim type_truck As String

If distance <= 30 And Weight <= 1200 Then
type_truck = "Large Van"
ElseIf distance > 30 And Weight > 1200 And Weight <= 7500 Then
type_truck = "Large Truck 10 - 20t"
ElseIf distance > 30 And Weigth > 7500 And Weight <= 13000 Then
type_truck = "Large Truck > 20t"
Else
type_truck = "LHV"
End If

Range("AP" & i).Value = type_truck

Next i

End Sub

Answer 3

对于使用动态数组和IFS的O365，这应该可以工作。

AP4 = IFS(AND(AO4:AO750 <= 30, AN4:AN750 <= 1200), "Large Van",
AND(AO4:AO750 > 30, AN4:AN750 > 1200, AN4:AN750 <= 7500), "Large Truck 10 - 20t",
AND(AO4:AO750 > 30, AN4:AN750 > 7500, AN4:AN750 <= 13000), "Large Truck > 20t",
TRUE, "LHV")

结果将溢出到第5到750行。

Answer 4

如果您对非VBA方法感兴趣，您可以使用此公式并将其向下拖动:

=IF(AND($AO4<30,$AN4<=1200),"Large Van",IF(AND($AO4>30,$AN4>1200,$AN4<=7500),"Large Truck 10 - 20t",IF(AND($AO4>30,$AN4>7500,$AN4<=13000),"Large Truck > 20t","LHV")))

或者如果你想要一个VBA解决方案，你可以这样做:

Sub test()
Sheet1.Range("AP4:AP750").Formula = "=IF(AND($AO4<30,$AN4<=1200),""Large Van"",IF(AND($AO4>30,$AN4>1200,$AN4<=7500),""Large Truck 10 - 20t"",IF(AND($AO4>30,$AN4>7500,$AN4<=13000),""Large Truck > 20t"",""LHV"")))"
End Sub

假设您希望在sheet1的单元格AP4到AP750中使用公式。