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

 

[Fluff]

What exactly is the error message you get?

 

[dinsyp]

What exactly is the error message you get?

Its the "No cells were found" error.

 

[Fluff]

In that case there are no empty cells in that column. Do you think there should be?

 

[dinsyp]

In that case there are no empty cells in that column. Do you think there should be?

There are empty cells in each column. For example, the Last Name column (D) has empty cells. The first set of code for User ID (B) works. The following columns don't which is puzzling me.

 

[Fluff]

For example, the Last Name column (D) has empty cells.

Not if you are getting the no cells found error, there must be something in them. What does this formula return

=ISBLANK(D4)

change the D4 to look at an "empty" cell in that column

 

[dinsyp]

Tested and the result comes back true for a blank cell. So no formula or hidden data.

 

[Fluff]

Is that before the last row of data in col D?

 

[dinsyp]

Yes. In the first blank row in the data.

 

[Fluff]

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