PDA

View Full Version : VBA VLOOKUP



tope10
05-10-2016, 09:23 AM
I'm trying to populate worksheet cells with some fields from another worksheet on the same workbook using VBA VLOOKUP.

I have two tables in separate sheets (1) and (2).

Sheet: (1)


Column 1

Column 2



000111

1



000222

2



000333

3



000444

4





Sheet: (2)



Column 1

Column 2



000222

8







Sub VLU()

With Sheets("1").Range("A2", Sheets("1").Cells(Rows.Count, "A").End(xlUp))

.Offset(, 1).Formula = "=VLOOKUP(A" & .Row & ",'2'!$A:$B,2,FALSE)"
.Offset(, 1).Value = .Offset(, 1).Value

End With
End Sub


Using the code above I can get the values that are different to update on sheet: (1), but I cannot for the life of me figure out how to get the items that do not have an update to not change. They go to either #N/A or 0. So after running this I get the table below:



Column 1

Column 2



000111




000222

8



000333




000444






I've also tried the following code for the formula but I just don't know what I need to put in to get the original value.


"=IF(ISERROR(VLOOKUP(A" & .Row & ",'2'!$A:$B,2,FALSE))," & Range("B" & .Row) & ",VLOOKUP(A" & .Row & ",'2'!$A:$B,2,FALSE))"

Any help is greatly appreciated