Vlookup in VBA - returning a cell reference

[MisterProzilla]

Hi everyone,

I have two sheets listing staff names, one for a broad overview and one specifically for tracking long-term sickness. When staff attend a review meeting in the long-term sheet, details are entered in a designated form-ish area, then the staff row is clicked and the 'Add Review' button is clicked, then it updates relevant fields in the selected staff row. That all works fine.

What I'd also like it to do is update the overview sheet with the meeting review date too. I'm trying to use a Vlookup within VBA to return the target review meeting cell for the matching ID, but I'm not sure if and how you can use vlookup to return a cell reference in this way, or if it just works with values? And if Vlookup won't work for this, is there an alternative?

Here's what I've got - the 'CalR' line in bold is where it's bringing up an error - 'Runtime error 91 - object variable or With block variable not set'.

Any help would be appreciated

Sub AddReview()

'Enter details of the review meeting in an area at the top of the sheet, then click a staff row and click the button and it updates the staff row's review record as below

Dim CalR As Range

'Looks for the matching ID(B/ActiveRow) in the Calendar sheet (Sheet1) to try and find the cell to enter a review date. In the Calendar, col A is ID, col I is the review dates.
[B]CalR = Application.WorksheetFunction.VLookup(Range("B" & ActiveCell.Row).Value, Sheet1.Range("A11:I300"), 9, False)[/B]

If ActiveCell.Row < 9 Then
MsgBox "Please select a record from the table below", vbOKOnly
Exit Sub
End If


' Copy the Meeting date (E5) into the Most Recent Review cell (F/ActiveRow)
Range("F" & ActiveCell.Row).Value = Range("E5").Value

' If enabled in the Workings sheet(Sheet3), updates the Informal review date cell in the Calendar (Sheet1)
If Sheet3.Range("BJ15").Value = "Yes" Then
CalR.Value = Range("E5").Value
End If

' Copy the meeting details (G6) into the Review dump(G/ActiveRow), with line break, followed by a copy of the current contents (col K)
Range("G" & ActiveCell.Row).Value = Range("G6").Value & Chr(10) & Range("K" & ActiveCell.Row).Value



End Sub

 

[sergioMabres]

Hiprozila
I think you could try changing the vlookup for
CalR = WorksheetFunction.VLookup(Range("Sheet2!B" & ActiveCell.Row), Range("Sheet1!A11:I300"), 9, False)

Cheers
Sergio

 

[MisterProzilla]

Cheers for the reply Sergio,

I figured it out in the end without needing the Vlookup:

Sub AddReview()

'Enter details of the review meeting in an area at the top of the sheet, then click a staff row and click the button and it updates the staff row's review record as below

Dim rngSearch As Range
Dim rngFind As Range

Set rngSearch = Sheet5.Range("B" & ActiveCell.Row)
Set rngFind = Sheet1.Columns(1).Find(What:=rngSearch.Value)


If ActiveCell.Row < 9 Then
MsgBox "Please select a record from the table below", vbOKOnly
Exit Sub
End If


' Copy the Meeting date (E5) into the Most Recent Review cell (F/ActiveRow)
Range("F" & ActiveCell.Row).Value = Range("E5").Value

'If auto-update enabled in the Workings sheet(Sheet3), updates the Informal review date cell in the Calendar (Sheet1)
If Sheet3.Range("BJ15").Value = "Yes" Then
rngFind.Offset(ColumnOffset:=8).Value = Range("E5").Value
End If

' Copy the meeting details (G6) into the Review dump(G/ActiveRow), with line break, followed by a copy of the current contents (col K)
Range("G" & ActiveCell.Row).Value = Range("G6").Value & Chr(10) & Range("K" & ActiveCell.Row).Value



End Sub