Boolean Variable from a Vlookup()

me-gjb

New Member
Joined
May 22, 2006
Messages
19
I am trying to find the syntax for determining if a field on my current worksheet matches a field on another worksheet. Note, there are over 12000 records to check.

I can currently designate a field on the current worksheet, then do a =IF(iserror(Vlookup))), followed by a copy and paste special, selecting value, then create a variable from that value, but there has to be a simpler way.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
one way of doing it is doing the following on sheet1

=if(countif('Sheet2!'A:A,a2)>0,"True","False")

By doing this, it will check the range and if it is 1 or more, it will return True, if not then it will return False.
 
Upvote 0
Not what I'm looking for. Let me give you the code, and explain the problem.

The program goes through a loop, matching a field in 'DataSheet' to a field in 'Log'. If the record is not in 'Log', I want to copy some of the information from 'DataSheet' to 'Log'. The procedure below goes through and identifies the matched/unmatched records, but the if statment does not work because the cell is a formula, and not the '0' value for false. I can insert a copy then do a paste special, but that would take forever. I'm looking for a quicker way.

Note: yrow_Counter represents the first empty row of the 'Log' worksheet.


Sheets("DataSheet").Select
xrow_Counter = 2
LastRow = Worksheets("DataSheet").Range("B65536").End(xlUp).Row
Do Until xrow_Counter = LastRow + 1
Range("A" & xrow_Counter).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[1],OrgName,1,FALSE)),""0"",""1"")"
If ActiveCell.FormulaR1C1 = "0" Then
Org_Name = Range("B" & xrow_Counter)
Sup_Code = Range("E" & xrow_Counter)
Aprv_Date = Range("F" & xrow_Counter)
Pnt_Name = Range("I" & xrow_Counter)
Sheets("Log").Select
Range("B" & yrow_Counter).Select
ActiveCell.FormulaR1C1 = Org_Name
Range("C" & yrow_Counter).Select
ActiveCell.FormulaR1C1 = Sup_Code
Range("D" & yrow_Counter).Select
ActiveCell.FormulaR1C1 = Aprv_Date
Range("G" & yrow_Counter).Select
ActiveCell.FormulaR1C1 = Pnt_Name
yrow_Counter = yrow_Counter + 1
Sheets("DataSheet").Select
End If
xrow_Counter = xrow_Counter + 1
Loop
 
Upvote 0
Instead of using Activecell.FormulaR1C1 try activecell.value and see what happens.


Change:
Code:
if Activecell.FormulaR1C1 = "" then

To:
Code:
if Activecell.Value = "0" then

I would also change all of the .FormulaR1C1's to .Value since they are not actual formulas but a value you are placing in there. This is within the If..Then statement and not the one above the if then statement.
 
Upvote 0
Code:
Sheets("DataSheet").Select 
   xrow_Counter = 2 
   LastRow = Worksheets("DataSheet").Range("B65536").End(xlUp).Row 

Do Until xrow_Counter = LastRow + 1 
   Range("A" & xrow_Counter).Select 
   ActiveCell.FormulaR1C1 = _ 
   "=IF(ISERROR(VLOOKUP(RC[1],OrgName,1,FALSE)),""0"",""1"")" 
      
      If ActiveCell.Value = "0" Then 
        Org_Name = Range("B" & xrow_Counter) 
        Sup_Code = Range("E" & xrow_Counter) 
        Aprv_Date = Range("F" & xrow_Counter) 
        Pnt_Name = Range("I" & xrow_Counter) 
        
        Sheets("Log").Select 
        Range("B" & yrow_Counter).Select 
        ActiveCell.Value = Org_Name 
        Range("C" & yrow_Counter).Select 
        ActiveCell.Value = Sup_Code 
        Range("D" & yrow_Counter).Select 
        ActiveCell.Value = Aprv_Date 
        Range("G" & yrow_Counter).Select 
        ActiveCell.Value = Pnt_Name 
        yrow_Counter = yrow_Counter + 1 
        Sheets("DataSheet").Select 
   End If 

   xrow_Counter = xrow_Counter + 1 

Loop
 
Upvote 0
That works great. Thank you.

However, Is there a way to bypass placing a value on the sheet, then reading the sheet, to trigger the if statement?
 
Upvote 0
If you are talking about having the macro automatically go check the second sheet without have the 0 or 1 formula put in place, you can loop through the items of the one sheet.

so you can do a for statement and have it do another for statement within it that will check the other sheet for that value. If it finds it then you can have it put the data on your "Log" sheet and you exit the for for it to go to next cell.

Also, on your code, instead of doing the select and then using activecell, you could put it all in one step of

Code:
sheets("Log").range("B" & yrow_counter).value = org_name
 
Upvote 0
VBA Chart modification/Chart naming

The single line of code you've suggested is a welcome addition.

As a user of excel, I am teaching myself VBA and macros, using the internet. This method provides no formal structure, and strictly means 'find a way as needed'. My education to date has not exposed me to the For..Each..Next statement. However, at your suggestion, I've started looking things up. I have a vague idea how it works, but will need time to do trial and error.

Thank you for your help.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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