Excel VBA 宏：定位列中的第一个空单元格并自动填充它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12375127/
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 VBA macro: Locating first empty cell in a column and automatically filling it
提问by user1663562
I have two columns, Column (A) and Column (B) in a spreadsheet.
我在电子表格中有两列，列 (A) 和列 (B)。
Column (A) contains names extracted from a query (ex. Brian, Bob, Bill, etc...) and column (B) contains one of three statuses (Assigned, In Progress, or Pending).
列 (A) 包含从查询中提取的名称（例如 Brian、Bob、Bill 等...），列 (B) 包含三种状态之一（已分配、进行中或待处理）。
However, this query sometimes pulls up some line items showing "Assigned" for the status with no name, therefore corresponding cell representing the name in Column (A) is blank. So I manually fill in those empty cells with "Unknown".
但是，此查询有时会为没有名称的状态拉出一些显示“已分配”的行项目，因此在列 (A) 中表示名称的相应单元格为空。所以我用“未知”手动填充那些空单元格。
What I want to do is to create a macro that finds the every empty cell in column (A) and fill in the word "Unknown" if the cell to its right contains the word "Assinged".
我想要做的是创建一个宏来查找 (A) 列中的每个空单元格，如果右侧的单元格包含“Assinged”一词，则填写“未知”一词。
So the conditions are:
所以条件是：
Blank cell in column (A)
Correspoding cell to its right (column B) contains the word "assinged"
列 (A) 中的空白单元格
其右侧的对应单元格（B 列）包含“assinged”一词
This is my Code:
这是我的代码：
Private Sub CommandButton2_Click()
For Each cell In Columns("A")
If ActiveCell.Value = Empty And ActiveCell.Offset(0, 1).Value = "Assigned" Then ActiveCell.Value = "Unknown"
Next cell
End Sub
采纳答案by Scott Holtzman
Welcome to SO.
欢迎来到 SO。
Try this code. It will work a bit faster and should get you what you want.
试试这个代码。它会工作得更快一点，并且应该可以满足您的需求。
Update: Made the code more bullet proof!
更新：使代码更加防弹！
Private Sub CommandButton2_Click()
Dim cel As Range, rngFind As Range, rngFilter As Range
Dim wks As Worksheet
Set wks = Sheets("sheet1")
With wks
'-> Error check to make sure "blanks" exist
Set rngFind = .Range("A1:A" & .Range("B" & Rows.Count).End(xlUp).Row).Find("", lookat:=xlWhole)
If Not rngFind Is Nothing Then
Set rngFilter = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)
rngFilter.AutoFilter 1, "="
'-> Error check to make sure "assigned" exists for blank cells
Set rngFind = .Columns("B:B").SpecialCells(xlCellTypeVisible).Find("Assigned", lookat:=xlWhole)
If Not rngFind Is Nothing Then
'-> okay, it exists. filter and loop through cells
rngFilter.AutoFilter 2, "Assigned"
Set rngFind = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(1)).SpecialCells(xlCellTypeVisible)
For Each cel In rngFind
If cel.Offset(0, 1).Value = "Assigned" Then cel.Value = "Unknown"
Next cel
End If
End If
End With
End Sub
回答by Reafidy
There is no need to loop here, take advantage of excels built in methods which will execute faster.
这里不需要循环，利用内置方法的 excel 可以更快地执行。
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.AutoFilter Field:=1, Criteria1:=""
.AutoFilter Field:=2, Criteria1:="Assigned"
If WorksheetFunction.CountBlank(.Columns(1)) > 0 Then
If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
.Columns(1).SpecialCells(xlCellTypeBlanks).Value = "Unknown"
End If
End If
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
回答by Brad
If you only need to do this a few times you could
如果你只需要这样做几次，你可以
- format your used range as a table
- on column A filter to only show "(Blanks)"
- on column B filter to only show "assinged"
- select all the resulting cells in column B
- press alt+ :to select only the visible cells
- press F2
- type "unknown"
- press ctrl+ enter
- 将您使用的范围格式化为表格
- 在 A 列过滤器上仅显示“（空白）”
- 在 B 列过滤器上只显示“assinged”
- 选择 B 列中的所有结果单元格
- 按alt+:仅选择可见单元格
- 按 F2
- 输入“未知”
- 按ctrl+enter
Your bad data should be good now!
你的坏数据现在应该好了！
Obviously this is a non-vba based solution but if you can avoid coding it's probably for the best.
显然，这是一个非基于 vba 的解决方案，但如果您可以避免编码，它可能是最好的。
