Store location of cell address to variable in VBA

问题:

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

回答1:

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

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