我会尽力解释我的问题。我需要基于在单元格（列）中出现的四个值之一填充值。我有一个密码
Sub testmacro()
If Sheets("List1").Range("A1") = "Keyword1" Then
Sheets("List1").Range("b1") = "60"
Sheets("List1").Range("c1") = "630"
Sheets("List1").Range("d1") = "0.7"
Sheets("List1").Range("e1") = "0.7"
ElseIf Sheets("List1").Range("A1") = "Keyword2" Then
Sheets("List1").Range("b1") = "1500"
Sheets("List1").Range("c1") = "15750"
Sheets("List1").Range("d1") = "1.46"
Sheets("List1").Range("e1") = "1"
ElseIf Sheets("List1").Range("A1") = "Keyword3" Then
Sheets("List1").Range("b1") = "1500"
Sheets("List1").Range("c1") = "15750"
Sheets("List1").Range("d1") = "2.98"
Sheets("List1").Range("e1") = "1"
ElseIf Sheets("List1").Range("A1") = "Keyword4" Then
Sheets("List1").Range("b1") = "1500"
Sheets("List1").Range("c1") = "15750"
Sheets("List1").Range("d1") = "2.38"
Sheets("List1").Range("e1") = "1"
End If
If Sheets("List1").Range("A2") = "Keyword1" Then
Sheets("List1").Range("b2") = "60"
Sheets("List1").Range("c2") = "630"
Sheets("List1").Range("d2") = "0.7"
Sheets("List1").Range("e2") = "0.7"
ElseIf Sheets("List1").Range("A2") = "Keyword2" Then
Sheets("List1").Range("b2") = "1500"
Sheets("List1").Range("c2") = "15750"
Sheets("List1").Range("d2") = "1.46"
Sheets("List1").Range("e2") = "1"
ElseIf Sheets("List1").Range("A2") = "Keyword3" Then
Sheets("List1").Range("b2") = "1500"
Sheets("List1").Range("c2") = "15750"
Sheets("List1").Range("d2") = "2.98"
Sheets("List1").Range("e2") = "1"
ElseIf Sheets("List1").Range("A2") = "Keyword4" Then
Sheets("List1").Range("b2") = "1500"
Sheets("List1").Range("c2") = "15750"
Sheets("List1").Range("d2") = "2.38"
Sheets("List1").Range("e2") = "1"
End If
。。。等等 。。。如您所见，此代码仅可使用2行，但是如果我需要10000行怎么办？不可能这样写。我需要可以穿过A列的内容，注意寻找“关键字”，然后用正确的值填充其余的行。谢谢您的帮助！（是的，Iam是VBA的新手）
您可以通过使用With语句来组织事物来简化事物。还添加Select Case语句以清除If - Elseif - End If表示法。然后添加For循环使事情更容易键入。循环时，请注意，您可以使用.Cells注解而不是.Range（例如Sheets(1).Range("B5")，与Sheets(1).Cells(5, 2)
例如：
For row = 1 to 2
With Sheets("List1")
Select Case .Cells(row,1)
Case "Keyword1"
.Cells(row,2) = "60"
.Cells(row,3) = "630"
.Cells(row,4) = "0.7"
.Cells(row,5) = "0.7"
Case "Keyword2"
.Cells(row,2) = "1500"
.Cells(row,3) = "15750"
.Cells(row,4) = "1.46"
.Cells(row,5) = "1"
Case "Keyword3"
.Cells(row,2) = "1500"
.Cells(row,3) = "15750"
.Cells(row,4) = "2.98"
.Cells(row,5) = "1"
Case "Keyword4"
.Cells(row,2) = "1500"
.Cells(row,3) = "15750"
.Cells(row,4) = "2.38"
.Cells(row,5) = "1"
end select
end with
next col
要对任意数量的行执行此操作，只需将行更改为For如下所示：
For row = 1 to 10000 ' if you know the exact amount or...
numRows = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("List1").Columns(1))
For row = 1 to numRows
'do the rest
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句