Store location of cell address to variable in VBA
82,721
The .Address
property returns a string so you'll need to to set the addCell
variable like so:
Set addCell = Worksheets("Schedule").Range(.Address)
Author by
Harry12345
Updated on January 24, 2020Comments
-
Harry12345 over 4 years
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