Replacing values in cell in one workbook with another workbook

[jamesdean1379]

Okay this one is a little tricky and i have been trying to play around with it but can't seem to get it to work correct. I have one workbook that i need to replace the cell values with another workbook.

example

Cell B3 on workbook A needs to look up on workbook B Column B and once found replaced with value of cell C on that row.

So it needs to take whats in cell B3 on workbook A and search Column B in workbook B and once found match need to replace cell B3 with what is in cell C on workbook B.

I realize this is going to be a huge code because i need it to do for all of column B in workbook A and i am not asking for the whole code, just how to go about it.

I was thinking of using a =if statement but couldn't get it to lookup and replace properly. Any help will be appreciated, if you need more information please ask and i will provide.

 

[JLGWhiz]

Sub replaceValue()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range, fn As Range
Set wb1 = Workbooks(1) 'Edit workbook name
Set wb2 = Workbooks(2) 'Edit workbook name
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
    For Each c In Range("B3:B" & lr)
        Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                c = fn.Offset(0, 1).Value
            End If
    Next
    Set fn = Nothing
End Sub

The code is not that big, but you have some work to do to make it run properly. Where you see the 'Edit' comments, you need to replace the numbers in parentheses with the names of the objects. Workbooks would be formatted like: Workbooks("Data.xlsx") for example, and sheets like eg. Sheets("results"). Also this code assumes that both workbooks will be open when the code is run. The code should be copied to the standard code module 1 of the workbook containing the source items, or workbook A by your definition.

 

[jamesdean1379]

JLGWHIZ i tried this and added the names of the workbooks/sheets and changed a few of the cell/column letters but the code stops running on the below code with the error "Object Required" Is the "For Each E in Range("E2:E" & lr)" for WB1 correct?

For Each E In Range("E2:E" & lr)[COLOR=#0000CD]        Set fn = sh2.Range("C:C").Find(A.Value, , xlValues, xlWhole)[/COLOR]
            If Not fn Is Nothing Then
                A = fn.Offset(0, 1).Value
            End If
    Next
    Set fn = Nothing

 

[JLGWhiz]

JLGWHIZ i tried this and added the names of the workbooks/sheets and changed a few of the cell/column letters but the code stops running on the below code with the error "Object Required" Is the "For Each E in Range("E2:E" & lr)" for WB1 correct?

For Each E In Range("E2:E" & lr)[COLOR=#0000CD]        Set fn = sh2.Range("C:C").Find(A.Value, , xlValues, xlWhole)[/COLOR]
            If Not fn Is Nothing Then
                A = fn.Offset(0, 1).Value
            End If
    Next
    Set fn = Nothing

You didn't need to change anything except the workbook and worksheet names. Some of the other things you changed were variables that told VBA where to look for the data and what to do with it. Try it with just the workbook and worksheet changes and see it it does what you want.

 

[jamesdean1379]

You didn't need to change anything except the workbook and worksheet names. Some of the other things you changed were variables that told VBA where to look for the data and what to do with it. Try it with just the workbook and worksheet changes and see it it does what you want.

Worked had to rearrange some columns on my sheets but works like a charm. Is there anyway to code it so I do not have to have both workbooks open, or is that a whole different monster?

 

[jamesdean1379]

Okay now i am getting confused. I have it working the way you coded it. Now I need to do the same thing except with Column D and E on WB1 and have it lookup on WB2 in Column B and replace columns D and E on WB1 with what is in Column A on WB2. I tried changing the codes a little to get it to work, but i am getting error object needed. Any idea what i did wrong? I changed the ranges to the columns on WB1, in WB2 range is still B:B for look up and pulling info from cell A.

Set wb1 = Workbooks("WFServlet.xls")
Set wb2 = Workbooks("LOC_CODES.xlsx")
Set sh1 = wb1.Sheets("WFServlet")
Set sh2 = wb2.Sheets("code")
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
    For Each c In Range("D2:D" & lr)
        Set fn = sh2.Range("B:B").Find(A.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                A = fn.Offset(0, 1).Value
            End If
    Next
    Set fn = Nothing

Set wb1 = Workbooks("WFServlet.xls")
Set wb2 = Workbooks("LOC_CODES.xlsx")
Set sh1 = wb1.Sheets("WFServlet")
Set sh2 = wb2.Sheets("code")
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
    For Each c In Range("E2:E" & lr)
        Set fn = sh2.Range("B:B").Find(A.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                A = fn.Offset(0, 1).Value
            End If
    Next
    Set fn = Nothing