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

Starbuck1975

New Member
Joined
Sep 10, 2014
Messages
5
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)
Index
Item Code
Other Data
Other Data
Actual Item Name
1
ABC123
Other data
Other data
Initially blank
2
ABC123
Other data
Other data
Initially blank
3
XYZ123
Other data
Other data
Initially blank
4
XYZ123
Other data
Other data
Initially blank
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
100,000
AYZ321
Other data
Other data
Initially blank

<tbody>
</tbody>

















Sheet 2 (Lookup)

Index
Item Code
Actual Item Name
1
ABC123
Beer
2
XYZ123
Pizza
.
.
.
.
.
.
.
.
.
250
MYZ123
Burgers

<tbody>
</tbody>












After running the macro, Sheet 1 would now show:
Index
Item Code
Other Data
Other Data
Actual Item Name
1
ABC123
Other data
Other data
Beer
2
ABC123
Other data
Other data
Beer
3
XYZ123
Other data
Other data
Pizza
4
XYZ123
Other data
Other data
Pizza
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
100,000
AYZ321
Other data
Other data
Other

<tbody>
</tbody>
















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
 
Last edited:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,805
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
Sep 10, 2014
Messages
5
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

New Member
Joined
Sep 10, 2014
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
Sep 10, 2014
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,330
Messages
5,528,043
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top