CODEWITHSUNDEEP
excelvba
Abner Aguayo
Abner Aguayo

Reputation: 15

Vlookup in vba to find values between two worksheets

I'm trying to grab the values from a different worksheet and match them to the their sister data in my main sheet in column A but I'm having issues with getting the right results, I was thinking of going the Vlookup route but I can't quite get it to work properly. I found a funky way of getting it done but I'm trying to save just the values and not the formula itself.

This is what I tried at first

Sub matchID()
'Dim wb As Workbook
'Set wb = ActiveWorkbook
'
'With wb.Sheets("Data")
'    .Range("E2:E" & .Range("A" & .Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP(A2,ID!A:B,2,FALSE)"
'End With
'the above works but need to save values and not formula

It kinda works but I need that values and not the formula, my plan is to find the data I need and then save a copy of the file as a csv

I tried using a different method but I'm running into runtime error '1004' I'm still learning VBA so I feel like I'm spinning my wheels right now. Can someone show me what I'm doing wrong?

Sub matchID()
'this is what I'm trying to get to work but unsure if I will still end up with formula and not just values

Dim result As String
Dim sheet As Worksheet
Dim lrow As Integer
Dim i As Integer
Set sheet = ActiveWorkbook.Sheets("Data")

lrow = sheet.UsedRange.Rows(sheet.UsedRange.Rows.Count).Row

For i = 2 To lrow
    result = Application.WorksheetFunction.VLookup("A2", Sheets("ID").Range("A:B"), 2, False)
    Cells(i, 5).Value = result
Next
End Sub

I'm trying to lookup all IDs(in column B) from my second sheet("ID") using the values in column A from my primary sheet("Data") and then populate the all results in column E in my primary sheet to their match.

My first try kinda worked but instead of leaving just the value it leaves the formula in the cell e.g. =VLOOKUP(A2,ID!A:B,2,FALSE) when really I'm looking for just the value 8447 that it shows before clicking on the cell.

Upvotes: 0

Views: 60

Answers (1)

dwirony
dwirony

Reputation: 5450

If you want to get rid of the formula, just paste as values:

Sub matchID()
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    With wb.Sheets("Data")
        .Range("E2:E" & .Range("A" & .Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP(A2,ID!A:B,2,FALSE)"
        .Range("E2:E" & .Range("A" & .Rows.Count).End(xlUp).Row).Value = .Range("E2:E" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    End With
End Sub

Upvotes: 2

Related Questions