将单元格地址的位置存储在VBA中的变量中 [英] Store location of cell address to variable in VBA

问题描述

我在Excel中使用VBA，我正在使用一个函数来找到第一个空行，然后添加一些值，之后我需要将单元格的地址传递给另一个函数，但是使用下面的代码获得一个运行时错误。 firstEmptyRow是返回范围的函数，例如。 $ A $ 280 。

  Dim addCell as Range 
 
'查找表中的第一个空行
 With firstEmptyRow 
 
'输入值
 .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 //给出运行时错误
 
结束
  

保存单元格地址的正确方法是什么，以便将其传递给另一个函数？以下是firstEmptyRow的代码

 公共函数firstEmptyRow（）As Range'返回计划表中的第一个空行
 
 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
 
对于i = 3 To sheet.Range（coltoSearch& Rows.Count）.End（xlUp）。行
设置r = sheet.Range（coltoSearch& i）
如果Len（r.Value）= 0然后
设置firstEmptyRow = sheet.Range（r.Address）
 'r.Select 
 Exit For'找到第一个空行结束循环
 End If 
 Next i 
 
'Debug.PrintfirstEmptyRow time： &安培; GetTickCount  - 时间，ms
 
结束函数
  

解决方案

.Address 属性返回一个字符串，因此您需要设置 addCell 变量如下：

 设置addCell = Worksheets（Schedule）。Range（.Address）
  

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.

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

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

解决方案

The .Address property returns a string so you'll need to to set the addCell variable like so:

Set addCell = Worksheets("Schedule").Range(.Address)

这篇关于将单元格地址的位置存储在VBA中的变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！