vba Excel 宏：循环和追加
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6671077/
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
Excel macro : loop and append
提问by D.Pimienta
I am a total newbie when it comes to VBA and excel though, not to programming in general. It seems that I am having quite a time trying to decipher VBA.
虽然我是 VBA 和 excel 的新手，但一般来说不是编程。看来我花了很长时间试图破译 VBA。
What I want to do, is loop through an unknown number of rows in a column and then if it contains a specific acronym it will check the same row in a different column it will append one of two strings depending on the second cell's contents. This code doesn't seem to work but perhaps it will help elucidate what I'm getting at.
我想要做的是循环遍历列中未知数量的行，然后如果它包含特定的首字母缩略词，它将检查不同列中的同一行，它将根据第二个单元格的内容附加两个字符串之一。这段代码似乎不起作用，但也许它会帮助阐明我的意思。
Sub AppendMacro()
'
'Append Macro
'Keyboard Shortcut: Ctrl+l
'
Dim c As Range
For Each c In Range("S:S")
If c.Value = "USAA" Or c.Value = "U.S.A.A" Then
ActiveCell.Offset(0, 2).Select
If ActiveCell.Value = "AM" Then
ActiveCell.Value = ActiveCell.Value & "8-10"
End If
End If
Next c
End Sub
One thing I know is that the value of the cell isn't exactly going to be USAA or U.S.A.A but will contain those sets of characters. Also, I'm sure I'm misunderstanding how ActiveCell works but if anything will set me straight it'll be you guys.
我知道的一件事是，单元格的值并不完全是 USAA 或 USAA，而是将包含这些字符集。另外，我确定我误解了 ActiveCell 的工作原理，但如果有什么能让我直截了当，那就是你们。
回答by Jean-Fran?ois Corbett
The only reason your code didn't work is because you forgot to activate the appropriate cell before calling ActiveCell.
您的代码不起作用的唯一原因是您在调用ActiveCell.
If c.Value = "USAA" Or c.Value = "U.S.A.A" Then
c.Activate 'Aha!
ActiveCell.Offset(0, 2).Activate
[etc.]
Of course, as pointed out by @GSerg, this Selectand ActiveCellbusiness is bad practice and makes your life more complicated. @GSerg's solution works fine.
当然，正如@GSerg 所指出的，这Select和ActiveCell业务是不好的做法，会让你的生活变得更加复杂。@GSerg 的解决方案工作正常。
You mention that
你提到
the value of the cell isn't exactly going to be USAA or U.S.A.A but will contain those sets of characters.
单元格的值不完全是 USAA 或 USAA，而是包含这些字符集。
Therefore I thought it would be appropriate to show how you can anticipate typos and make your code accept things like "US A A", "U.S.A,A"or "U..S.A.. ,A".
因此，我认为展示如何预测拼写错误并使代码接受诸如"US A A","U.S.A,A"或"U..S.A.. ,A".
Dim c As Range
Dim s As String
For Each c In Range("S:S").Cells
' Get the cell content
s = c.Value
' Cleanse it of "noise" characters
s = Replace(s, ".", "")
s = Replace(s, ",", "")
s = Replace(s, " ", "")
s = Replace(s, "whatever other characters may pollute USAA", "")
' Does the cleansed string contain "USAA"?
If InStr(s, "USAA") <> 0 Then ' wink to @Issun
With c.Offset(0, 2)
If .Value = "AM" Then .Value = .Value & "8-10"
End With
End If
Next c
回答by GSerg
Dim c As Range
For Each c In Range("S:S").Cells
If instr(c.Value, "USAA") > 0 Or instr(c.Value, "U.S.A.A") > 0 Then
With c.Offset(0, 2)
If .Value = "AM" then .Value = .Value & "8-10"
End With
End If
Next c
InStrfinds first instance of a string inside another string.ActiveCellis the currently selected cell in the active window, not the loop counter. It is advisable to avoid usingActiveCell(and.Select) in code, unless you actually want the user to select a cell and then act upon it.With...End Withis just a handy way to temporarily capture a reference toc.Offset(0, 2), to avoid explicit variable for it or calling it three times in a row. Without this block, it would beIf c.Offset(0, 2).Value = "AM" Then c.Offset(0, 2).Value = c.Offset(0, 2).Value & "8-10" End If
InStr在另一个字符串中查找字符串的第一个实例。ActiveCell是活动窗口中当前选定的单元格，而不是循环计数器。建议避免在代码中使用ActiveCell(和.Select)，除非您确实希望用户选择一个单元格然后对其进行操作。With...End With只是一种临时捕获对 的引用的方便方法c.Offset(0, 2)，以避免为它显式变量或连续调用它 3 次。如果没有这个块，它将是If c.Offset(0, 2).Value = "AM" Then c.Offset(0, 2).Value = c.Offset(0, 2).Value & "8-10" End If
