在 VBA 中测试单元格是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16733855/
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
Testing if a cell is empty in VBA
提问by Doug
I'm trying to write a module in VBA to take a worksheet containing the structure of a website and output into a worksheet two columns which have (column 1) the name of a page, and (column 2) the section of the site that page is in. (So I can make use of it in Visio)
我正在尝试在 VBA 中编写一个模块，以将包含网站结构的工作表输出到工作表中，其中两列具有（第 1 列）页面名称和（第 2 列）该站点的部分页面在。（所以我可以在 Visio 中使用它）
My existing worksheet contains a structure like this:
我现有的工作表包含这样的结构：
Root / Section / Sub-section / Page
Root / Section / Page
Root / Section / Sub-section / Sub-section / Page
i.e. each row shows the page's ancestors in the columns to the left, but there are different levels to the structure complicating matters.
即每一行在左边的列中显示页面的祖先，但结构复杂化有不同的层次。
I'm trying to loop through this, starting in column 7 (the deepest level of navigation), and looping left initially to find a non-empty cell, and then copying that cell and its parent into my new sheet. Once it hits column 2 I want it to move down, as this contains the root; this carries on until the last row at 562.
我试图从第 7 列（导航的最深层次）开始循环遍历，最初向左循环以找到一个非空单元格，然后将该单元格及其父单元复制到我的新工作表中。一旦它到达第 2 列，我希望它向下移动，因为它包含根；这一直持续到最后一行 562。
My code is below, but it's giving me an error on my 'While Isempty' line. What have I done wrong?
我的代码在下面，但它在我的“While Isempty”行上给了我一个错误。我做错了什么？
Sub directDescendent()
Dim readCol As Integer
Dim readRow As Integer
Dim writeRow As Integer
readCol = 7
readRow = 2
writeRow = 2
While readRow < 562
While IsEmpty(ActiveWorkbook.Sheets(2).Cells(readRow, readCol).Value)
readCol = readCol - 1
Wend
If readCol < 3 Then
readCol = 7
readRow = readRow + 1
End If
ActiveWorkbook.Sheets(3).Cells(writeRow, 1).Value = ActiveWorkbook.Sheets(2).Cells(readRow, readCol).Value
ActiveWorkbook.Sheets(3).Cells(writeRow, 2).Value = ActiveWorkbook.Sheets(2).Cells(readRow, readCol - 1).Value
writeRow = writeRow + 1
Wend
End Sub
回答by
Your readColvariable's value is 0 (zero) when the code enters While IsEmpty(ActiveWo...the 7th time (min value there should be 1) so an exception is thrown as index is out of bounds (asking for the 0th element of something that has elements 1 to n)
readCol当代码进入While IsEmpty(ActiveWo...第 7 次时，您的变量的值为 0（零）（最小值应为 1），因此当索引超出范围时会引发异常（要求具有元素的事物的第 0 个元素1 to n）
回答by dee
You can use for-next with step = -1. While-wend is not necessary because the number of itereations is known (7).
您可以将 for-next 与 step = -1 一起使用。While-wend 不是必需的，因为迭代次数是已知的 (7)。
For readCol = 7 To 1 Step -1
If Not IsEmpty(ActiveWorkbook.Sheets(2).Cells(readRow, readCol).Value) Then Exit For
Next readCol
'
' Note:
' Here readCol would be equeal to zero if all cells from column 7 to 1 are empty.
'
