VBA 初学者：大于
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14238061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Beginner to VBA: Greater than
提问by AlexHunt
I have numerical values entered in Row 1 from columns Ato IA. I want to create a loop that compares one cell with the cell before it (aka Cell B1to A1or cell Fto E). Let's use B1and A1as the example. It looks at the Value in Cell B1and sees if it is greater then the value of the cell in A1. If it is greater then I want a +to be entered in the Cell B2. Also if B1is < A1put a -into Cell B2. I want the program to be able to loop this process so it does it for all the columns A-AI. Below is what I want want the program to do (not including the dashes and teh paranthesis around the positive and negative signs of course).
我在第 1 行从列A到IA. 我想创建一个循环，将一个单元格与其之前的单元格（又名 Cell B1toA1或 cell Fto E）进行比较。让我们以B1和A1为例。它查看单元格中的值B1并查看它是否大于 中单元格的值A1。如果它更大，那么我想+在 Cell 中输入a B2。此外，如果B1是< A1把一个-单元格B2。我希望该程序能够循环此过程，以便它对所有列执行此操作A-AI。以下是我希望程序执行的操作（当然，不包括正负号周围的破折号和括号）。
A B C D F 1 33.12 34.52 34.92 35.19 34.97 2 (+) (+) (+) (-)
I realize this task is easily performed in excel (not using VBA) but I am trying to learn VBA so I can perform much more complex tasks. I have written the basic code to do the simple task but I am not sure how to loop it so it will do this for all my cells!
我意识到这个任务很容易在 excel 中执行（不使用 VBA），但我正在尝试学习 VBA，这样我就可以执行更复杂的任务。我已经编写了完成简单任务的基本代码，但我不确定如何循环它，因此它会为我的所有单元格执行此操作！
Sub EnterFormula()
Dim x As Integer
Dim y As Integer
x = Worksheets("Sheet2").Range("C2").Value
y = Worksheets("Sheet2").Range("B2").Value
If x > y Then
Worksheets("Sheet2").Range("C4") = "+"
End If
End Sub
Ok So for the next part of My Program. It gets a touch more complicated. We move onto row 3. Row 3 is going to either have a U (for Up) or a D (for down) or nothing.
好的，我的程序的下一部分。它变得更复杂。我们移动到第 3 行。第 3 行将有一个 U（表示向上）或一个 D（表示向下）或什么都没有。
Let's Start at Column C. Column C1 has a value of 34.92, and C2 was given a + (as 34.92 was larger then the day before which was 33.02). Now we go to the first previous "+" WITH AT LEAST one opposite sign (in this case "-") in between. So in this case that is row A (one "-" inbetween under row B). Now if the Numerical Value in C1 (34.92) is larger then the numerical value in A (33.12) then we designate a "U" in C3. If it was NOT larger we would leave an empty cell in C3.
让我们从 C 列开始。C1 列的值为 34.92，C2 被赋予 +（因为 34.92 比前一天的 33.02 大）。现在我们转到第一个前面的“+”，中间至少有一个相反的符号（在本例中为“-”）。所以在这种情况下，这是 A 行（B 行下的一个“-”）。现在，如果 C1 (34.92) 中的数值大于 A (33.12) 中的数值，那么我们在 C3 中指定一个“U”。如果它不是更大，我们将在 C3 中留下一个空单元格。
Let's move onto column D. Column D1 has a value of 35.19 which is greater then the C1 value of 34.92 and this is why D2 has a "+". Next we go to the first previous "+" WITH AT LEAST one opposite sign (in this case "-") in between. So in this case that is row A again. Since the numerical value in D1 (39.19) is greater then the numerical value in A1 (33.12) then D3 gets a U.
让我们转到 D 列。D1 列的值为 35.19，它大于 C1 的值 34.92，这就是 D2 具有“+”的原因。接下来，我们转到前一个“+”，中间至少有一个相反的符号（在本例中为“-”）。所以在这种情况下，又是 A 行。由于 D1 (39.19) 中的数值大于 A1 (33.12) 中的数值，因此 D3 得到 U。
Moving onto Column F (32.97)...Note:I changed the value a little from the original F. 32.97 is LESS then 35.19 (D1) which is why F2 is a "-". Next we go to the first previous "-" WITH AT LEAST one opposite sign (in this case "+") in between. So in this case this is Row B (with two "+" signs in between). Now because we are dealing with "-" signs this time we look and see if the numerical value in F1 is LESS then the numerical value in B1...which it is, so a "D" is entered in F3. If F1 was larger then B1 then the cell would be left empty.
移至 F 列 (32.97)...注意：我从原始 F 中稍微更改了值。32.97 小于 35.19 (D1)，这就是 F2 是“-”的原因。接下来，我们转到前一个“-”，中间至少有一个相反的符号（在本例中为“+”）。所以在这种情况下，这是 B 行（中间有两个“+”号）。现在，因为我们这次处理的是“-”符号，所以我们查看 F1 中的数值是否小于 B1 中的数值......因此，在 F3 中输入“D”。如果 F1 大于 B1，则该单元格将留空。
Onto Column G (35.21). This is greater then 32.97 (F1) and therefore gets a "+" in G2. Next we go to the first previous "+" WITH AT LEAST one opposite sign in between (in this case "-"). So in this case this is Row D (with one "-" in between). Since the numerical value of G1 is greater then that of D1 we designate a "U". If it was not greater we would leave the cell empty.
到 G 列 (35.21)。这大于 32.97 (F1)，因此在 G2 中得到“+”。接下来，我们转到前一个“+”，中间至少有一个相反的符号（在本例中为“-”）。所以在这种情况下，这是 D 行（中间有一个“-”）。由于 G1 的数值大于 D1 的数值，我们指定一个“U”。如果它不是更大，我们会将单元格留空。
A B C D F G H I 1 33.12 33.02 34.92 35.19 32.97 35.21 35.60 35.90 2 (+) (-) (+) (+) (-) (+) (+) (+) 3 U U D U U U
Here is my code so far for this. I have added to my original code which was creating the "+" signs and "-" signs.
到目前为止，这是我的代码。我已添加到创建“+”号和“-”号的原始代码中。
Sub Comparison()
Dim targetCell As Range
Dim targetSignCell As Range
Dim currentSign As String
Dim currentNumericalCell As Currency
' Find out what sign (+ or -) the current Cell has in it
currentSign = Worksheets("Sheet2").Range("H3").Value
'Variable to associate the numerical number above the current Cell
currentNumericalCell = Worksheets("Sheet2").Range("H2").Value
' Here we iterate through each cell in a specified range
' Since you know you want to start at B1 and go until E1,
' you can ues the following syntax to go through each cell
For Each Cell In Range("B2:H2")
' Get the value of the current cell with the .Value property
currentValue = Cell.Value
' Now get the value of the cell that is before it (column-wise) previousValue = Cell.Offset(0, -1).Value
' 现在获取它之前的单元格的值（按列） previousValue = Cell.Offset(0, -1).Value
' Create a variable for our target cell
Set targetCell = Cell.Offset(1, 0)
' Here are the basic comparisons
If currentValue > previousValue Then
targetCell.Value = "+"
ElseIf currentValue < previousValue Then
targetCell.Value = "-"
ElseIf currentValue = previousValue Then
targetCell.Value = "="
Else
' Not sure how it would happen, but this
' is your catch-all in case the comparisons fail
targetCell.Value = "???"
End If
' Now go to the next cell in the range
Next Cell
'Alex starting to code
For Each Cell In Range("H3:B3")
' Find out what the sign is in the cell before it
previousSign = Cell.Offset(0, -1).Value
'Variable used to find the first cell with an
'Opposite sign as the current cell
oppositeSign = Cell.Offset(0, -2).Value
'Variable to associate the numberical number above the first Opposite Sign Cell
oppositeNumericalCell = Cell.Offset(-1, -2).Value
' Create a Variable for Target Cell
Set targetSignCell = Cell.Offset(1, 0)
If currentSign.Value = "+" And currentSign.Value <> previousSign.Value And oppositeSign.Value = currentSign.Value And currentNumericalCell.Value > oppositeNumericalCell.Value Then
targetSignCell = "U"
ElseIf currentSign.Value = "-" And currentSign.Value <> previousSign.Value And oppositeSign.Value = currentSign.Value And currentNumericalCell.Value < oppositeNumericalCell.Value Then
targetSignCell = "D"
Else
End If
Next Cell
End Sub
回答by RocketDonkey
I agree with @JohnBustos that a formula would be much more efficient, however if this is indeed a learning exercise then here is a simple example that would do what you want:
我同意@JohnBustos 的观点，即公式会更有效，但是，如果这确实是一项学习练习，那么这里有一个简单的示例，可以满足您的需求：
Sub Comparison()
Dim targetCell As Range
' Here we iterate through each cell in a specified range
' Since you know you want to start at B1 and go until E1,
' you can ues the following syntax to go through each cell
For Each cell In Range("B1:E1")
' Get the value of the current cell with the .Value property
currentValue = cell.Value
' Now get the value of the cell that is before it (column-wise)
previousValue = cell.Offset(0, -1).Value
' Create a variable for our target cell
Set targetCell = cell.Offset(1, 0)
' Here are the basic comparisons
If currentValue > previousValue Then
targetCell.Value = "+"
ElseIf currentValue < previousValue Then
targetCell.Value = "-"
ElseIf currentValue = previousValue Then
targetCell.Value = "="
Else
' Not sure how it would happen, but this
' is your catch-all in case the comparisons fail
targetCell.Value = "???"
End If
' Now go to the next cell in the range
Next cell
End Sub
And if you were to do it as a formula, it could be something like this (entered into B2and copied to the end of the range):
如果你把它作为一个公式来做，它可能是这样的（输入B2并复制到范围的末尾）：
=IF(B1>A1,"+",IF(B1<A1,"-","="))
This compares the cell above the formula and the cell to the left of that cell and adds the appropriate symbol.
这将比较公式上方的单元格和该单元格左侧的单元格并添加适当的符号。
回答by RocketDonkey
Assuming there are no empty cells in the range you want to work in, you could do it like this:
假设您要处理的范围内没有空单元格，您可以这样做：
Range("b2").Select
Do Until IsEmpty(ActiveCell.Offset(-1, 0))
If ActiveCell.Offset(-1, 0).Value > ActiveCell.Offset(-1, 1).Value Then
ActiveCell.Formula = "+"
End If
If ActiveCell.Offset(-1, 0).Value < ActiveCell.Offset(-1, 1).Value Then
ActiveCell.Formula = "-"
End If
ActiveCell.Offset(0, 1).Select
Loop
If there are empty cells in the range then instead of 'do until' use
如果范围内有空单元格，则不要使用“直到”
dim I
for I = 1 to ..
next I
