VBA VLOOKUP to fill in blank cells in a row from another worksheet

[dinsyp]

Hi People.

I have a worksheet which contains some rows with missing data which I wish to fill from a another worksheet. As can be seen in the image below, there are several row cells with little data in them apart from the ParticipantID. In a few instances, only the UserID is missing.

The source worksheet has the data formatted like:

Employee Number

User ID

First Name

Last Name

Job Title

Employee Type

Employee Status

Region

Site

I want to fill in the missing cells using a Vlookup based on the ParticipantID/Employee Number as the reference for the VLookup (they're the same number). I have tried using the code below but after the first cell is filled in (UserID), when I try to move the VLookup to the next column (D or Last Name), I get the 1004 error. Wondering what I'm missing. BTW, there are currently 60000 rows in the data set.

Sub MyLookupMacro()

Dim lastrow As Long

' Step 1 Fill in where all the user info is missing
' Find last row with data in column D

'UserID
Range("B1:B" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],InactiveStaff!R2C1:R16321C9,2,FALSE),0)"

' Find last row with data in column E
lastrow = Cells(Rows.Count, "D").End(xlUp).Row
'LastName
Range("D1:D" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-3],InactiveStaff!R2C1:R16321C9,4,FALSE),0)"

' Find last row with data in column E
lastrow = Cells(Rows.Count, "E").End(xlUp).Row
'FirstName
Range("E1:E" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-5],InactiveStaff!R2C1:R16321C9,3,FALSE),0)"

' Find last row with data in column F
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
'JobTitle
Range("F1:F" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-6],InactiveStaff!R2C1:R16321C9,5,FALSE),0)"

' Find last row with data in column I
lastrow = Cells(Rows.Count, "I").End(xlUp).Row
'Region
Range("I1:I" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-8],InactiveStaff!R2C1:R16321C9,8,FALSE),0)"

' Find last row with data in column K
lastrow = Cells(Rows.Count, "K").End(xlUp).Row
'Site
Range("K1:K" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-10],InactiveStaff!R2C1:R16321C9,9,FALSE),0)"

' Step 2 Fill in where only the UserID is missing
' Find last row with data in column B
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

' Populate all blank cells in column B with formula
Range("B1:B" & lastrow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],InactiveStaff!R2C1:R16321C9,2,FALSE),0)"

End Sub

Thanks

Andrew

 

[dinsyp]

In that case I cannot explain it. If there are genuinely empty cells in the column you should not get the error you reported.

Thanks for looking into it. I've gone down a manual path of filtering for blank entries and pasting the VLookups into the first cells and copying down. Seems to be working fine so will leave well enough along.

 

[Fluff]

Glad you sorted it & thanks for the feedback.