Solved VBA to replace Vlookup

Microsoft Office 2010 home and student
April 2, 2013 at 06:33:09
Specs: Windows XP
I have a similar case for this one that I searched around (http://www.computing.net/answers/office/search-a-field-and-assign-a-label-based-on-results/17987.html), yet not find a solution.
I have the main spreadsheet with several info collected such as ref #, date, hours, and so on. Then another spreadsheet contains ref # that need to mark as "good" in the main spreadsheet.

I could manually add column for status (good/take a look in the spreadsheet 2), then use Vlookup to function in in the main spreadsheet. However, I would like the VBA code for it to automatically put "good" on the ref # in spreadsheet 2 in main spreadsheet and "take a look" if the ref # is different from main spreadsheet and spreadsheet 2 (status column are already created).
Ex:
Main spreadsheet:

 
    A              B                                  C 
1 5964         02/02/12             Drop list of "good", "take a look" .... 
2 6588         02/02/12                     Good or take a look
3 1285        12/12/12                      Good or take a look

Spreadsheet 2:

 
         A                  B
1      1285               Good
2      8562               Good


See More: VBA to replace Vlookup

Report •


✔ Best Answer
April 3, 2013 at 11:21:42
Sub FindRef()
'Determine last row with data on Main sheet
  lastMainRw = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row
'Determine last row with data on Sheet2
  lastSheet2Rw = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
'Loop through Main Column A and search for Ref # in Sheet2 Column A
 For nextRef = 1 To lastMainRw
  With Sheets("Sheet2").Range("A1:A" & lastSheet2Rw)
    Set r = .Find(Sheets("Main").Range("A" & nextRef))
     If Not r Is Nothing Then
      Sheets("Main").Range("C" & nextRef) = "Good"
     Else
      Sheets("Main").Range("C" & nextRef) = "Take A Look"
     End If
  End With
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 2, 2013 at 06:56:36
First, based on the example data that you posted, I don't understand why "8652" has "Good" next to it. I don't see 8652 in the Main spreadsheet. Keep in mind that we can't see your spreadsheet from where we're sitting, so all we can go on is the example data that you post.

Second, why do you want VBA when a VLOOKUP seems like it would work?

In Sheet2!B1, enter this and drag it down.

=IF(ISNA(VLOOKUP(A1,Main!$A$1:$A$3,1,0)),"Take A Look","Good")

Based solely on your example data, you should get:

         A                  B
1      1285               Good
2      8562               Take A Look
 

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 2, 2013 at 07:33:20
I may mislead you. I actually want to run the formula in the main spreadsheet, and every ref # in the spreadsheet 2 is all "good" status. I want the formula to function in the main spreadsheet to figure out if the ref # is "good" or "take a look".

It does not matter if the ref # in spreadsheet 2 is not in the main spreadsheet, but any ref # in both spreadsheet should be marked as "good".

The reason I want VBA because new good ref # could come in anyway and it is nice to automate this. Additionally, there is no any other column in the spreadsheet 2, it is simply included only ref #. I had to manually add the column B.

Here is the spreadsheet 2 originally:

                     A                   
1                  1285
2                  8562



Report •

#3
April 2, 2013 at 08:57:40
Something is still not clear to me.

"The reason I want VBA because new good ref # could come in anyway and it is nice to automate this."

A new good ref # could "come into" where? Main or Sheet2?

Where in the sheet would it "come into"? At the end of Column A? As a replacement for an existing ref #? Someplace else?

"Additionally, there is no any other column in the spreadsheet 2, it is simply included only ref #. "

I don't see why that matters. VLOOKUP only needs one column. Based on your example data, if you want to see which ref #'s in Main Column A also appear in Sheet2 Column A, use this in Main!C1 and drag it down:

=IF(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,0)),"Take A Look","Good")

Based solely on your example data, you should get this in Main:

    
       A       B           C 
1    5964   02/02/12   Take A Look
2    6588   02/02/12   Take A Look
3    1285   12/12/12   Good

I'm not saying that VBA is a bad idea; I just haven't seen any reason why you would want to use it in this case. If the VLOOKUP isn't "automatic" enough, please explain why.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 2, 2013 at 09:13:06
I mean the new ref # status could change from "normal" to "good" status anytime. When the status is changed, I will receive a new spreadsheet that contains all ref # in a "good" status, both old and new ones.

The spreadsheet I received contains only ref # and I see what you mean in your formula. However, there are more than 2 items in the status column such as acceptable, excellent, or no need to look.

I want it to be in VBA because I know that there is some limitation for Vlookup when spreadsheets contain more than 50,000 line items. Am I right? I am worried it may take too long with Vlookup function only and I also want the VBA to look at the new spreadsheet every time (instead I had to manually update the formula everytime I receive new spreadsheet).

Thank you so much for those help. I hope I could clarify myself better this time.


Report •

#5
April 2, 2013 at 09:46:53
No, you haven't clarified much. In fact, you've made it even more confusing, at least to me.

"I mean the new ref # status could change from "normal" to "good" status anytime "

Where did "normal" come from? There was no mention of "normal" in any of your other posts.

"I will receive a new spreadsheet that contains all ref # in a "good" status, both old and new ones. "

Which spreadsheet will be "new"...Main or Sheet2?

"However, there are more than 2 items in the status column such as acceptable, excellent, or no need to look."

So now there are more requirements than originally stated? I thought it was either "Good" or "Take A Look". Now you are talking about "normal", "Acceptable", etc.

I no longer have any clue what you are trying to accomplish.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 2, 2013 at 10:47:25
Ok. My bad.

There are several status in the system. However, I am at the step 1 of the process. By step 1, I mean I need to recognize it is either "good" or "Take a look". Then it will go to other steps before it is done.

By saying it is normal status, then change to good- I mean there are some ref # that is fine to put in "Take a look" status in step 1. However, it could change anytime during the year.

To answer your question, Sheet 2 will be new. I will receive a different workbook when there is change in status, then I import it by replacing sheet 2 by new one.

So all I want to accomplish is to create a VBA code to do the following:

1. Do step 1- recognize ref # status either "good" or "take a look"
2. Could run it again every time I update Sheet 2

Again, I think it may run faster with VBA, and if possible, I later want to develop it to updating Sheet 2 for me as well. I now do not understand VBA well enough to do that, so I try to start slowly. Hope I clarify something this time. Sorry for all the confusion I gave to you.


Report •

#7
April 3, 2013 at 07:36:52
DerbyDad, could you help me out please?

Report •

#8
April 3, 2013 at 09:20:51
If it has more than 300k rows, I could not run with VLOOKUP. Please help me out.

Report •

#9
April 3, 2013 at 11:21:42
✔ Best Answer
Sub FindRef()
'Determine last row with data on Main sheet
  lastMainRw = Sheets("Main").Range("A" & Rows.Count).End(xlUp).Row
'Determine last row with data on Sheet2
  lastSheet2Rw = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
'Loop through Main Column A and search for Ref # in Sheet2 Column A
 For nextRef = 1 To lastMainRw
  With Sheets("Sheet2").Range("A1:A" & lastSheet2Rw)
    Set r = .Find(Sheets("Main").Range("A" & nextRef))
     If Not r Is Nothing Then
      Sheets("Main").Range("C" & nextRef) = "Good"
     Else
      Sheets("Main").Range("C" & nextRef) = "Take A Look"
     End If
  End With
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
April 3, 2013 at 16:23:54
Thank you, DerbyDad :D

Report •


Ask Question