vba 设置范围以在工作表中的所有填充行上运行宏

Setting a range to have a Macro run on all populated rows in a worksheet

提问by Stoney

I've pieced together a macro to allow me to calculate the cost of a story task by calculating the specific rate based on the developer assigned. I have the rate table on a second sheet. I am able to get a result for the cell that the macro is set to (Row 2), but want it to run on all rows. I know I have to set a generic range, but am not sure. How should I change the range declare to run on all rows?
Here is the code:

我拼凑了一个宏，让我可以通过根据分配的开发人员计算特定费率来计算故事任务的成本。我在第二张纸上有费率表。我能够获得宏设置为（第 2 行）的单元格的结果，但希望它在所有行上运行。我知道我必须设置一个通用范围，但我不确定。我应该如何更改范围声明以在所有行上运行？
这是代码：

Sub GetCost()
  Range("D2").Select
  ' Set Do loop to stop when an empty cell is reached.
  Do Until IsEmpty(ActiveCell)
    Dim Estimate As Integer, Assignee As String, RodRate As Integer, GarthRate As Integer, DerekRate As Integer, TotalCost As Integer

    Estimate = ThisWorkbook.Worksheets("Sheet1").Range("D2").Value
    Assignee = ThisWorkbook.Worksheets("Sheet1").Range("E2").Value
    RodRate = ThisWorkbook.Worksheets("Sheet2").Range("B2").Value
    GarthRate = ThisWorkbook.Worksheets("Sheet2").Range("B3").Value
    DerekRate = ThisWorkbook.Worksheets("Sheet2").Range("B4").Value

    If Assignee = "Rod" Then
        TotalCost = Estimate * RodRate
    ElseIf Assignee = "Garth" Then
        TotalCost = Estimate * GarthRate
    ElseIf Assignee = "Derek" Then
        TotalCost = Estimate * DerekRate
    Else
        TotalCost = "0"

    End If

    ThisWorkbook.Worksheets("Sheet1").Range("F2").Formula = TotalCost
  ActiveCell.Offset(1, 0).Select
  Loop
End Sub

回答by Tony Dallimore

I have rewritten your code with explanations which I hope are enough for you to understand why. There is much more that I could say. I hope this is a good balance between too little and too much.

我已经用解释重写了你的代码，我希望这些解释足以让你理解为什么。我能说的还有很多。我希望这是太少和太多之间的良好平衡。

However, I have to point out that there are some excellent project management tools available. I do not believe this is a good use of your time.

但是，我必须指出，有一些出色的项目管理工具可用。我认为这不是很好地利用你的时间。

Random points

随机点

On 32-bit computers, Long is better than Integer.

在 32 位计算机上，Long 优于 Integer。

Do not declare your variables inside a loop. The scope of a variable declared inside a sub-routine is the the sub-routine so declare them at the top of the sub-routine.

不要在循环中声明变量。在子例程中声明的变量的范围是子例程，因此在子例程的顶部声明它们。

You can declare all your variables in a single Dimstatement but I find it confusing unless there is a real association between two or more variable. I might have:

您可以在一条Dim语句中声明所有变量，但除非两个或多个变量之间存在真正关联，否则我觉得这很令人困惑。我可能有：

Dim RodRate As Long, GarthRate As Long, DerekRate As Long

because these variables are associated. However the trouble with this approach is that you will have to add MaryRateand JohnRateand AngelaRatewhen these people join your project.

因为这些变量是相关联的。但是这种方法的问题是你必须添加MaryRate并JohnRate和AngelaRate当这些人加入您的项目。

You need an array:

你需要一个数组：

Dim PersonRate(1 To 3) As Long

where PersonRate(1)= Rate for Rod, PersonRate(2)= Rate for Garth and PersonRate(3)= Rate for Derek.

其中PersonRate(1)= Rod 的比率，PersonRate(2)= Garth 的PersonRate(3)比率和= Derek 的比率。

But this is hardly any better. You want a table that can grow. So today:

但这也好不到哪里去。你想要一张可以增长的桌子。所以今天：

   Name   Rate
   Rod    20
   Garth  25
   Derek  15

Next week:

下周：

   Name   Rate
   Rod    20
   Garth  25
   Derek  15
   Mary   30

With this, you pick up the Assignee's name, run down the table until you find their name then look across for their rate.

有了这个，你拿起受让人的名字，在桌子上跑，直到你找到他们的名字，然后查看他们的费率。

I assume you have a table like this in Sheet2. You could keep going back to Sheet2 but better to load the table into an array.

我假设您在 Sheet2 中有一个这样的表。您可以继续返回 Sheet2，但最好将表加载到数组中。

We could have:

我们可以有：

Dim PersonName() As String
Dim PersonRate() As Long

so PersonRate(2)gives the rate for PersonName(2).

所以PersonRate(2)给出了 的比率PersonName(2)。

Note in my first array declaration I wrote: PersonRate(1 To 3). This time, the brackets are empty. With PersonRate(1 To 3), I am saying I want exactly three entries in the array and this cannot be changed. With PersonRate(), I am saying I want an array but I will not know how many entries until run time.

注意，在我的第一个数组声明我写道：PersonRate(1 To 3)。这一次，括号是空的。使用PersonRate(1 To 3)，我是说我想要数组中的三个条目，并且无法更改。使用PersonRate()，我是说我想要一个数组，但直到运行时我才知道有多少条目。

I said we could have two arrays, PersonName()and PersonRate()and this is what I have done. This is an easy-to-understand approach but I do not think it is the best approach. I prefer structures. When you have got this macro working and before you start your next look up User Typeswhich is the VBA name for a structure.

我说我们可以有两个数组，PersonName()而PersonRate()这就是我所做的。这是一种易于理解的方法，但我认为这不是最好的方法。我更喜欢结构。当你让这个宏工作时，在你开始下一次查找之前User Types，哪个是结构的 VBA 名称。

Consider:

考虑：

   With Sheets("Sheet2")
     RowMax = .Cells(Rows.Count, "A").End(xlUp).Row
   End With

There is a lot to explain here.

这里有很多东西要解释。

Cellsmeans I want to address a cell within the active workbook. .Cellsmeans I want to address a cell within the sheet identified in the With statement. This means I do not have to select Sheet1 or Sheet2 to look at their contents. Selecting worksheets is slow and the code tends to be more difficult to understand.

Cells意味着我想对活动工作簿中的一个单元格进行寻址。.Cells意味着我想在 With 语句中标识的工作表中寻址一个单元格。这意味着我不必选择 Sheet1 或 Sheet2 来查看它们的内容。选择工作表很慢，而且代码往往更难理解。

.Cells(Row, Column)identifies a cell. Row must be a number but column can be a number or a column code: A=1, B=2, Z=26, AA=27, etc.

.Cells(Row, Column)标识一个单元格。行必须是数字，但列可以是数字或列代码：A=1、B=2、Z=26、AA=27 等。

Rows.Countreturns the number of rows in a sheet for the version of Excel you are using. So .Cells(Rows.Count, "A")identifies the bottom of column "A".

Rows.Count返回您正在使用的 Excel 版本的工作表中的行数。所以.Cells(Rows.Count, "A")标识列“A”的底部。

End(xlUp)is the VBA equivalent of clicking Ctrl+UpArrow. If you are not familar with Ctrl+ArrowI suggest you play with these four controls. Note, these controls give easy to understand results with a rectangular table. However, if there are empty cells, the results can be strange.

End(xlUp)是相当于单击Ctrl+的 VBA UpArrow。如果你不熟悉Ctrl+Arrow我建议你玩这四个控件。请注意，这些控件使用矩形表格提供易于理解的结果。但是，如果有空单元格，结果可能会很奇怪。

Putting this together: .Cells(Rows.Count, "A").End(xlUp).Rowmeans start at the bottom of column A, go up until you hit a cell with a value and return its row number. So this sets RowMaxto the last row of the Rate table. When you add row 5 with Mary's name and rate, this code will automatically adjust.

把它们放在一起：.Cells(Rows.Count, "A").End(xlUp).Row意味着从 A 列的底部开始，一直向上直到你找到一个带有值的单元格并返回它的行号。所以这将设置RowMax为 Rate 表的最后一行。当您添加带有 Mary 的姓名和评分的第 5 行时，此代码将自动调整。

Revised code

修订代码

This should be enough to get you started. Welcome to the joys of programming.

这应该足以让您入门。欢迎来到编程的乐趣。

' * Require all variables to be declared which means a misspelt name
'   is not taken as an implicit declaration
Option Explicit

Sub GetCost()

   Dim Estimate As Integer
   Dim Assignee As String
   Dim TotalCost As Integer

   Dim PersonName() As String
   Dim PersonRate() As String
   Dim InxPerson As Long

   Dim RowCrnt As Long
   Dim RowMax As Long

   ' You can declare constants and use them in place of literals.
   ' You will see why later.  I could have made these strings and
   ' used "A", "B", "D", "E" and "F" as the values.  Change if that
   ' is easier for you.
   Const ColS2Name As Long = 1
   Const ColS2Rate As Long = 2
   Const ColS1Estimate As Long = 4
   Const ColS1Assignee As Long = 5
   Const ColS1Total As Long = 6

   ' Before doing anything else we must load PersonName and PersonRate from
   ' Sheet2.  I assume the structure of Sheet2 is:

   '     A      B
   '  1  Name   Rate
   '  2  Rod    20
   '  3  Garth  25
   '  4  Derek  15

   With Sheets("Sheet2")

     RowMax = .Cells(Rows.Count, ColS2Name).End(xlUp).Row

     ' I now know how big I want the the name and rate arrays to be
     ReDim PersonName(1 To RowMax - 1)
     ReDim PersonRate(1 To RowMax - 1)

     ' Load these arrays
     For RowCrnt = 2 To RowMax
       ' I could have used 1 and 2 or "A" and "B" for the column 
       ' but this is easier to understand particularly if you come
       ' back to this macro in six month's time.
       PersonName(RowCrnt - 1) = .Cells(RowCrnt, ColS2Name).Value
       PersonRate(RowCrnt - 1) = .Cells(RowCrnt, ColS2Rate).Value
     Next
   End With

   With Sheets("Sheet1")

     ' I am using the same variable for rows in sheets Sheet1 and Sheet2.
     ' This is OK because I never look at Sheet1 and Sheet2 at the same time.
     RowCrnt = 2

     Do Until IsEmpty(.Cells(RowCrnt, ColS1Estimate))
       Estimate = .Cells(RowCrnt, ColS1Estimate).Value
       Assignee = .Cells(RowCrnt, ColS1Assignee).Value
       .Cells(RowCrnt, ColS1Total).Value = 0
       ' Locate the Assignee in the PersonName array and
       ' extract the matching rate
       For InxPerson = 1 To UBound(PersonName)
         If PersonName(InxPerson) = Assignee Then
           .Cells(RowCrnt, ColS1Total).Value = Estimate * PersonRate(InxPerson)
           Exit For
         End If
       Next
       RowCrnt = RowCrnt + 1
     Loop
  End With

End Sub

回答by SWa

Tony's answer is a great solution and introduction to programming and very well written so I've +1 it. However unless I'm missing something code should always be the last resort in excel as it is very slow compared to formulas, I would have thought that a simple lookup would suffice, something like:

托尼的回答是一个很好的解决方案和编程介绍，写得很好，所以我已经+1了。但是，除非我遗漏了某些代码应该始终是 excel 中的最后手段，因为它与公式相比非常慢，否则我会认为简单的查找就足够了，例如：

=D2*(vlookup(E2,'sheet2'!A:B,2,FALSE)) 

Copied down the column

复制下来的列