multiple find and replace

[jmkerzic]

I have 2 tabs on the spreadsheet. I want to update column N (Location), with a new location which is on the 2nd tab.

I would use Column E as as the lookup value, and add the new location to column N. If the number is not found I want the the Location to stay as it is. Tab 2 is the updated location list.

Lookup e2 on tab1, and if it is on Tab 2 (column B) return with the value that is in Column c on Tab 1 column N. If that value is not found on Tab2, than do not update column N.

Example: If E2 is found on tab 2 it would return 011 (column C) to column N on tab 1. If not found it would change column N.
TAB 1

TAB 2

 

[mumps]

This macro assumes you have headers in row 1 and your data starts in row 2 in both sheets. Change the sheet names (in red) to suit your needs.

Sub UpdateLocation()
    Application.ScreenUpdating = False
    Dim Val As String, ws1 As Worksheet, ws2 As Worksheet, i As Long, v1 As Variant, v2 As Variant
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    v1 = ws1.Range("E2", ws1.Range("E" & Rows.Count).End(xlUp)).Value
    v2 = ws2.Range("B2", ws2.Range("B" & Rows.Count).End(xlUp)).Resize(, 2).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v2, 1)
            Val = v2(i, 1)
            If Not .Exists(Val) Then
                .Add Val, Nothing
            End If
        Next i
        For i = 1 To UBound(v1, 1)
            Val = v1(i, 1)
            If .Exists(Val) Then
                ws1.Range("N" & i + 1) = v1(i, 2)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[jmkerzic]

Thanks.

Got this error:

 

[mumps]

Try replacing:

ws1.Range("N" & i + 1) = v1(i, 2)

with

ws1.Range("N" & i + 1) = v2(i, 2)

 

[jmkerzic]

Awesome. Thank you so much for the help.

 

[mumps]

You are very welcome.

 

[jmkerzic]

I got the error again. The spreadsheet I am using has almost 19000 lines would that have anything to do with the macro working properly. I did a small sample of 75 it worked fine. Than when I added another 150 or so lines it went back to that error.

 

[mumps]

The number of rows shouldn't make any difference. Could you upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. (de-sensitized if necessary).

 

[jmkerzic]

MARJAN1.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

Try:

Sub UpdateLocation()
    Application.ScreenUpdating = False
    Dim Val As String, ws1 As Worksheet, ws2 As Worksheet, i As Long, v1 As Variant, v2 As Variant, dic As Object
    Set ws1 = Sheets("Marjan Inventory")
    Set ws2 = Sheets("Transfers")
    v1 = ws1.Range("E2", ws1.Range("E" & Rows.Count).End(xlUp)).Value
    v2 = ws2.Range("B2", ws2.Range("B" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(v2, 1)
        Val = v2(i, 1)
        If Not dic.Exists(Val) Then
            dic.Add Key:=Val, Item:=v2(i, 2)
        End If
    Next i
    For i = 1 To UBound(v1, 1)
        Val = v1(i, 1)
        If dic.Exists(Val) Then
            ws1.Range("N" & i + 1) = dic(Val)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub