Bulk Find & Replace from a Lookup Table with some conditional logic

[Starbuck1975]

My VB skills are basic at best, but I am trying to code a fairly straightforward iterative find and replace macro. The macro I wrote actually works, but crashes with an Overflow error after 30,000 iterations.

I have a data report that shows item usage for a group of users. This report resides on "Sheet1" and consists of over 100,000 rows of data. One column in this report provides an alphanumeric code that represents the item being used. I run a variety of pivot tables from this report and would like to pivot on item usage by the actual item's name instead of the alphanumeric code.

I have a second Sheet, "Sheet2", that translates the alphanumeric codes into the actual item names. There are 250 possibilities, which is why I am not doing a simple Find and Replace. This is a simple two column look-up table, but the data refreshes weekly, so it is not always 250 potential matches.

Ideally I want to dump the latest data into Sheet 1 every week, run the macro so I can pivot on the Item Name and then refresh all of my Pivot Tables with the new data.

I wrote a macro that takes the alphanumeric code from "Sheet1", compares it to every lookup code on "Sheet2", and if it finds a match, it writes the actual item name in a new column on "Sheet1". If it doesn't find a match, which is a possibility, it simply assigns a value of "Other". It keeps iterating until it hits a null cell, which means it has run out of data to check. The total index changes week to week, so I can't set the total iterations to a constant value.

Here is a basic example of how it should work:

Sheet 1 (Data)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Index
[/TD]
[TD]Item Code
[/TD]
[TD]Other Data
[/TD]
[TD]Other Data
[/TD]
[TD]Actual Item Name
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ABC123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Initially blank
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABC123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Initially blank
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]XYZ123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Initially blank
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]XYZ123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Initially blank
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD]100,000
[/TD]
[TD]AYZ321
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Initially blank
[/TD]
[/TR]
</tbody>[/TABLE]

















Sheet 2 (Lookup)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Index
[/TD]
[TD]Item Code
[/TD]
[TD]Actual Item Name
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ABC123
[/TD]
[TD]Beer
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]XYZ123
[/TD]
[TD]Pizza
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD]250
[/TD]
[TD]MYZ123
[/TD]
[TD]Burgers
[/TD]
[/TR]
</tbody>[/TABLE]












After running the macro, Sheet 1 would now show:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Index
[/TD]
[TD]Item Code
[/TD]
[TD]Other Data
[/TD]
[TD]Other Data
[/TD]
[TD]Actual Item Name
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ABC123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Beer
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]ABC123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Beer
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]XYZ123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Pizza
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]XYZ123
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Pizza
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[TD].
[/TD]
[/TR]
[TR]
[TD]100,000
[/TD]
[TD]AYZ321
[/TD]
[TD]Other data
[/TD]
[TD]Other data
[/TD]
[TD]Other
[/TD]
[/TR]
</tbody>[/TABLE]
















The code I wrote does what I need it to do even if it probably isn't the best way to do this, but gives an overflow error after 30,000 iterations, which I wrote as follows:

Dim i As Integer
Dim j As Integer
Dim App As String
Dim Code As String
Dim Family As String
Dim Match As Boolean

i = 2
j = 2
Match = False

Do
App = ActiveWorkbook.Worksheets("RawData").Cells(i, 6).Value

Do
Code = ActiveWorkbook.Worksheets("Applications").Cells(j, 1).Value
Family = ActiveWorkbook.Worksheets("Applications").Cells(j, 2).Value

If App = Code Then
Match = True
ActiveWorkbook.Worksheets("RawData").Cells(i, 20).Value = Family
ElseIf Code = vbNullString Then
Match = True
ActiveWorkbook.Worksheets("RawData").Cells(i, 20).Value = "Other"
Else
j = j + 1
End If
Loop Until Match = True

j = 2
Match = False
i = i + 1
Loop Until App = vbNullString

 

[StephenCrump]

I haven't looked at your code (given that you say it works) but ...

I do note that Integer variables won't accommodate values greater than 32,767. Try declaring these as Long.

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

The overflow error occurs because you've defined your counter variables as Integer data types.
The largest value that an Integer variable can hold is 32,767.
You could address that error by changing the data types to Long.

If speed is important to you, then this thread provides examples of some more efficient ways to accomplish this lookup.
http://www.mrexcel.com/forum/excel-...p-visual-basic-applications-code-vlookup.html

Another option (not necessarily faster, but it would eliminate the need for VBA) would be to use MS Query to setup a Query that will Join the two tables. You can use the result of that query as the data source for your Pivot Tables.

Just ask if you want some help implementing either of those approaches for your purpose.

 

[Starbuck1975]

Jerry and Stephen,

Thank you. Changing the counter to Long almost solved the problem of my code returning an overflow error after 32,000 iterations. I am not receiving an error now, but the code did simply terminate at 125000 iterations. It worked for those iterations. I will further explore VLOOKUP and MS Query as a better way of doing this.

 

[Starbuck1975]

Hi Jerry,

I have been playing with VLookup from above but cannot seem to get it to work. Using my example from above I wrote the VLookup function as follows:

VLookup(B2,Sheet2!A1:C250,3)

I believe what this function is saying is to take the data in cell B2 and compare it to every value in the table defined on Sheet2. If B2 is a match, then write the value in the 3rd column into the cell. I am getting a #REF! error. I found a few simplistic examples for VLookup online, but nothing where you take a value from one sheet, compare to a range of values in another sheet, and then return a matched value back to the original sheet.

 

[Jerry Sullivan]

The VLOOKUP function has an optional 4th parameter called "range_lookup"

That parameter must be included and evaluate to either FALSE or 0 to perform an exact match.
If it is omitted as it in your try, then VLOOKUP performs an approximate match which assumes your lookup table values are sorted in ascending order.

So try...
=VLOOKUP(B2,Sheet2!$A$1:$C$250,3,FALSE)

The "$" (absolute references) will allow you to copy down your formula without the address of your lookup table being changed.

The article at this link explains the VLOOKUP function in more detail.
VLOOKUP function - Excel

 

[Starbuck1975]

The VLOOKUP function has an optional 4th parameter called "range_lookup"

That parameter must be included and evaluate to either FALSE or 0 to perform an exact match.
If it is omitted as it in your try, then VLOOKUP performs an approximate match which assumes your lookup table values are sorted in ascending order.

So try...
=VLOOKUP(B2,Sheet2!$A$1:$C$250,3,FALSE)

The "$" (absolute references) will allow you to copy down your formula without the address of your lookup table being changed.

The article at this link explains the VLOOKUP function in more detail.
VLOOKUP function - Excel

Thanks Jerry, that did it. Can't believe I wasted so much time trying to write a macro when VLOOPUP does exactly what I needed it to do.