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
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