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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321
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.
 

me-gjb

New Member
Joined
May 22, 2006
Messages
19
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
 

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321
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.
 

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321

ADVERTISEMENT

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
 

me-gjb

New Member
Joined
May 22, 2006
Messages
19
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?
 

anthonya2369

Active Member
Joined
Mar 10, 2005
Messages
321
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
 

me-gjb

New Member
Joined
May 22, 2006
Messages
19
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,846
Messages
5,544,634
Members
410,626
Latest member
rkmadasu
Top