将单元格地址的位置存储到 VBA 中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24735803/
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
Store location of cell address to variable in VBA
提问by Harry12345
I'm using VBA in Excel and I'm using a function to find the first empty row then adding some values, after that I need to pass the address of the cell to another function but using the code below I get a runtime error. firstEmptyRow is a function that returns a range,e.g. $A$280.
我在 Excel 中使用 VBA，我正在使用一个函数来查找第一个空行，然后添加一些值，之后我需要将单元格的地址传递给另一个函数，但是使用下面的代码我得到一个运行时错误。firstEmptyRow 是一个返回范围的函数，例如$A$280。
Dim addCell as Range
'Find first empty row in the table
With firstEmptyRow
'Enter Values
.Value = taskID
.Offset(0, 1).Value = jobID
.Offset(0, 2).Value = jobName
.Offset(0, 6).Value = taskTypeID
.Offset(0, 8).Value = taskName
.Offset(0, 9).Value = desc
.Offset(0, 11).Value = estMins
.Offset(0, 13).Value = "No"
set addCell = .Address //Gives a runtime error
End With
What is the correct way to save the address of the cell so I can pass it to another function? Below is the code for firstEmptyRow
保存单元格地址以便我可以将其传递给另一个函数的正确方法是什么？下面是 firstEmptyRow 的代码
Public Function firstEmptyRow() As Range 'Returns the first empty row in the Schedule sheet
Dim i, time As Long
Dim r As Range
Dim coltoSearch, lastRow As String
Dim sheet As Worksheet
Set sheet = Worksheets("Schedule")
time = GetTickCount
coltoSearch = "A"
For i = 3 To sheet.Range(coltoSearch & Rows.Count).End(xlUp).Row
Set r = sheet.Range(coltoSearch & i)
If Len(r.Value) = 0 Then
Set firstEmptyRow = sheet.Range(r.Address)
'r.Select
Exit For 'End the loop once the first empty row is found
End If
Next i
'Debug.Print "firstEmptyRow time: " & GetTickCount - time, , "ms"
End Function
回答by Gareth
The .Addressproperty returns a string so you'll need to to set the addCellvariable like so:
该.Address属性返回一个字符串，因此您需要addCell像这样设置变量：
Set addCell = Worksheets("Schedule").Range(.Address)
回答by Vityata
You do not need to overcomplicate the code with something like Set addCell = Worksheets("Schedule").Range(.Address)because it really makes the readability a bit tough. In general, try something as simple as this: Set FirstEmptyRow = myCell. Then the whole code could be rewritten to this:
你不需要用类似的东西使代码过于复杂，Set addCell = Worksheets("Schedule").Range(.Address)因为它确实使可读性有点困难。一般来说，尝试像这样简单的事情：Set FirstEmptyRow = myCell。然后整个代码可以重写为：
Public Function FirstEmptyRow() As Range
Dim myCell As Range
Dim coltoSearch As String, lastRow As String
Dim wks As Worksheet
Set wks = Worksheets(1)
coltoSearch = "A"
Dim i As Long
For i = 3 To wks.Range(coltoSearch & Rows.Count).End(xlUp).Row
Set myCell = sheet.Range(coltoSearch & i)
If IsEmpty(myCell) Then
Set FirstEmptyRow = myCell
Exit For
End If
Next i
If i = 2 ^ 20 Then
MsgBox "No empty rows at all!"
Else
Set FirstEmptyRow = sheet.Range(coltoSearch & i + 1)
End If
End Function
The last part of the code makes sure, that if there is no empty cell before the last row and row number 3, then next cell would be given as an answer. Furthermore, it checks whether this next row is not the last row in Excel and if it is so, it gives a MsgBox()with some information.
代码的最后一部分确保，如果最后一行和第 3 行之前没有空单元格，则将给出下一个单元格作为答案。此外，它会检查下一行是否不是 Excel 中的最后一行，如果是，它会MsgBox()提供一些信息。
回答by Wayne Clark
I am a total "noob" at vba and learning by trying code snipits and routines that appear to to me to answer to a problem I am stuck on. When I tried the code posted by Vityata I encountered "Run-time error '424': Object Requiredat the following stmts:
我是 vba 的完全“菜鸟”，通过尝试代码片段和例程来学习，这些代码在我看来似乎可以回答我遇到的问题。当我尝试 Vityata 发布的代码时，我遇到了“运行时错误‘424’：在以下 stmts 中需要对象：
Set myCell = Sheet.Range(coltoSearch & i)
Set FirstEmptyRow = Sheet.Range(coltoSearch & i + 1)
Changing the stmts as follows resolved the errors for me:
如下更改 stmts 为我解决了错误：
Set myCell = wks.Range(coltoSearch & i)
Set FirstEmptyRow = wks.Range(coltoSearch & i + 1)
I also added a Debug.Print stmt between the last End If and the End Function stmts so I could quickly see the result when testing as follows:
我还在最后一个 End If 和 End Function stmts 之间添加了一个 Debug.Print stmt，所以我可以在测试时快速查看结果，如下所示：
End If
Debug.Print "The 1st empty cell address is "; coltoSearch & i
End Function
If I have violated some posting rule, please accept my apology. Hopefully sharing this will avoid future confusion by others who use a similar learning process.
如果我违反了某些发帖规则，请接受我的道歉。希望分享这一点将避免其他使用类似学习过程的人将来混淆。
