VBA Find and Replace all Instances of all Variables

[LCTrucido]

Hopefully this makes sense...

The first sheet we will call "Pick" and is formatted like this:

SKU	Description	Call Name	BC	QTY	B/O	Aisle	Row	Tier	Truck	Stop
1234567	Blah Blah	Blah	_____	1		301	A	1	1	1

<tbody>
</tbody>

There is an empty row under every entry. The headers above are for display and the sake of clarity, the actual Pick sheet is a daily data dump with 9 extra rows before the first entry.

The second sheet is "Route" which will also change daily, formatted as:

Area	Truck	Stop	Door	LS
Blah	1	1	702	1

<tbody>
</tbody>

What I'm trying to figure out is how to take each combo of Truck/Stop from the Route worksheet, find all instances of it in the Pick worksheet, and replace it with the corresponding Door/LS combo. The Pick example above would then become:

SKU	Description	Call Name	BC	QTY	B/O	Aisle	Row	Tier	Truck	Stop
1234567	Blah Blah	Blah	_____	1		301	A	1	702	1

<tbody>
</tbody>

I don't know what the logic would be to accomplish this so unfortunately I don't have relevant sample code yet.

 

[yky]

Is Truck the search term? Looks to me you want to take the truck number in "Route", find it in "Pick", and then do the replacement. This will only work if truck number is unique in "Route". If the same truck number appears more than once in "Route", there is a problem.

 

[LCTrucido]

Is Truck the search term? Looks to me you want to take the truck number in "Route", find it in "Pick", and then do the replacement. This will only work if truck number is unique in "Route". If the same truck number appears more than once in "Route", there is a problem.

The Truck number may have multiple entries in the Route sheet but the Stop number would be different for each one. Truck 1 - stop 1, Truck 1 - stop 2, etc.

 

[Jerry Sullivan]

Here's some code you can try. The range references assume that your screenshots start at cell A1 on each sheet.

Sub ReplaceValuesFromLookup()
 Dim lLastRowLookup As Long, lLastRowSource As Long, lNdx As Long
 Dim sMatchKey As String
 Dim vLookupTbl As Variant, vPickValues As Variant
 
 With Sheets("Route")
   lLastRowLookup = .Cells(.Rows.Count, "A").End(xlUp).Row
   
   '--read lookup table values into array (edit to match actual columns)
   vLookupTbl = .Range("A2:E" & lLastRowLookup).Value
 End With
 
 With Sheets("Pick")
   lLastRowSource = .Cells(.Rows.Count, "A").End(xlUp).Row
   
   '--read old values into array (edit to match actual columns)
   vPickValues = .Range("J2:K" & lLastRowSource).Value
   
 End With
 
 '--concatenate fields with delimiter to make lookup key in first column
 For lNdx = 1 To UBound(vLookupTbl, 1)
   vLookupTbl(lNdx, 1) = vLookupTbl(lNdx, 2) & "|" & vLookupTbl(lNdx, 3)
 Next
 
 '--replace old values based on lookup table matches
 For lNdx = 1 To UBound(vPickValues, 1)
   sMatchKey = vPickValues(lNdx, 1) & "|" & vPickValues(lNdx, 2)
   If Len(sMatchKey) > 1 Then
      vPickValues(lNdx, 1) = Application.vLookup(sMatchKey, vLookupTbl, 4, 0)
      vPickValues(lNdx, 2) = Application.vLookup(sMatchKey, vLookupTbl, 5, 0)
   End If
 Next
 
 '--write updated values back to worksheet
 Sheets("Pick").Range("J2").Resize(UBound(vPickValues, 1), _
   UBound(vPickValues, 2)).Value = vPickValues
 
End Sub

 

[LCTrucido]

Here's some code you can try. The range references assume that your screenshots start at cell A1 on each sheet.

Hi Jerry,

First, thank you. I was correct in thinking this was going to be beyond me and the help is appreciated. I tweaked the columns to match the actual sheet I'm working with, here is the code now:

Sub ReplaceValuesFromLookup() Dim lLastRowLookup As Long, lLastRowSource As Long, lNdx As Long
 Dim sMatchKey As String
 Dim vLookupTbl As Variant, vPickValues As Variant
 
 With Sheets("Route")
   lLastRowLookup = .Cells(.Rows.Count, "A").End(xlUp).Row
   
   '--read lookup table values into array (edit to match actual columns)
   vLookupTbl = .Range("B2:E" & lLastRowLookup).Value
 End With
 
 With Sheets("Pick Input")
   lLastRowSource = .Cells(.Rows.Count, "A").End(xlUp).Row
   
   '--read old values into array (edit to match actual columns)
   vPickValues = .Range("J2:K" & lLastRowSource).Value
   
 End With
 
 '--concatenate fields with delimiter to make lookup key in first column
 For lNdx = 1 To UBound(vLookupTbl, 1)
   vLookupTbl(lNdx, 1) = vLookupTbl(lNdx, 2) & "|" & vLookupTbl(lNdx, 3)
 Next
 
 '--replace old values based on lookup table matches
 For lNdx = 1 To UBound(vPickValues, 1)
   sMatchKey = vPickValues(lNdx, 1) & "|" & vPickValues(lNdx, 2)
   If Len(sMatchKey) > 1 Then
      vPickValues(lNdx, 1) = Application.VLookup(sMatchKey, vLookupTbl, 4, 0)
      vPickValues(lNdx, 2) = Application.VLookup(sMatchKey, vLookupTbl, 5, 0)
   End If
 Next
 
 '--write updated values back to worksheet
 Sheets("Pick Input").Range("J2").Resize(UBound(vPickValues, 1), _
   UBound(vPickValues, 2)).Value = vPickValues
 
End Sub

It runs without throwing any errors but when writing the updated values, each cell in corresponding cell in J and K is updated to "#N/A".

 

[Jerry Sullivan]

It runs without throwing any errors but when writing the updated values, each cell in corresponding cell in J and K is updated to "#N/A".

That means a match is not being found by Vlookup.

The code that I suggested read the values in Route!A:E" & lLastRowLookup into an array, then it replaced the values in the first column with Match Keys. In your revised code, you are using Route!B:E" & lLastRowLookup, which doesn't allow the keys to be stored in the first column.

Try revising back to Route!A:E" & lLastRowLookup and let me know if it still doesn't work.

 

[LCTrucido]

That means a match is not being found by Vlookup.

The code that I suggested read the values in Route!A:E" & lLastRowLookup into an array, then it replaced the values in the first column with Match Keys. In your revised code, you are using Route!B:E" & lLastRowLookup, which doesn't allow the keys to be stored in the first column.

Try revising back to Route!A:E" & lLastRowLookup and let me know if it still doesn't work.

Something so simple... I changed it back and it worked on the first try. If you ever find yourself near Boston I owe you a beer (or ten).

 

[Jerry Sullivan]

Will do!