VBA Find and Replace all Instances of all Variables

L

LCTrucido

Board Regular
Joined
Apr 29, 2011
Messages
88
Hopefully this makes sense...

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Description[/TD]
[TD]Call Name[/TD]
[TD]BC[/TD]
[TD]QTY[/TD]
[TD]B/O[/TD]
[TD]Aisle[/TD]
[TD]Row[/TD]
[TD]Tier[/TD]
[TD]Truck[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Blah Blah[/TD]
[TD]Blah[/TD]
[TD]_____[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]301[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Truck[/TD]
[TD]Stop[/TD]
[TD]Door[/TD]
[TD]LS[/TD]
[/TR]
[TR]
[TD]Blah[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]702[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Description[/TD]
[TD]Call Name[/TD]
[TD]BC[/TD]
[TD]QTY[/TD]
[TD]B/O[/TD]
[TD]Aisle[/TD]
[TD]Row[/TD]
[TD]Tier[/TD]
[TD]Truck[/TD]
[TD]Stop[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Blah Blah[/TD]
[TD]Blah[/TD]
[TD]_____[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]301[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]702[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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.
 
Upvote 0
yky said:
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.
Click to expand...

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.
 
Upvote 0
Here's some code you can try. The range references assume that your screenshots start at cell A1 on each sheet.

Code: 
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
 
Upvote 0
Jerry Sullivan said:
Here's some code you can try. The range references assume that your screenshots start at cell A1 on each sheet.
Click to expand...

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:

Code: 
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".
 
Upvote 0
LCTrucido said:
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".
Click to expand...

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.
 
Upvote 0
Jerry Sullivan said:
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.
Click to expand...

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).
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
VBA Find match, if found, copy rows and paste in different workbook
Replies
6
Views
461
MARK858
MARK858
D
  • Question Question
VBA Search for matching rows in another sheet & display all data in a table
Replies
4
Views
10K
dramqueenuk
D
N
  • Question Question
Export from Excel to Access via VBA based on cell Value
Replies
0
Views
1K
NewPadawan
N
J
  • Question Question
VBA to extract row data to sheet and update data set
Replies
0
Views
933
justatroy
J
T
  • Question Question
VBA to launch userform by selecting a cell
2
Replies
10
Views
2K
Logit
Logit

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top