Home/ Questions/Q 4419
Next
In Process
Computing Staff
  • 4
Asked: 2021-11-10T11:01:54+00:00 In:

VBA To Replace Vlookup

  • 4

I have a similar case for this one that I searched around (https://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
Share

    1 Answer

    1. 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
      • 0