Compare two ranges and put pass or fail

[Nick70]

Hi,

I need a macro that compares a range in column H with a range in column E (in same sheet) and if same text then put "pass" in column F otherwise put "fail".
If cells in column H are blank then don't put anything (so either skip comparison or put """").

See example below:

Column E	Column F		Column H
John	pass		John
Mike	pass		Mike
Luke	fail		XXX
Alex	pass		Alex
Sam	pass		Sam
Luke	fail		XXX
David	pass		David
Sam	pass		Sam
Oscar	pass		Oscar
Elliot	fail		XXX
Steve	pass		Steve
Jack	pass		Jack

I know this can easily be done with an Excel formula but I need a macro as this VBA code will go into a larger tool that will use this function so Excel formula would be of no use.

Many Thanks,
N.

 

[Fluff]

How about

Sub Nick70()
   With Range("F2:F" & Range("H" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate("if(" & .Offset(, 2).Address & "="""","""",if(" & .Offset(, 2).Address & "=" & .Offset(, -1).Address & ",""Pass"",""Fail""))")
   End With
End Sub

 

[Nick70]

PERFECTO!

Thanks a lot.
N.

 

[Fluff]

You're welcome & thanks for the feedback

 

[tmsousa]

Hi! I'm looking for something similar, but I would like to compare 2 columns in 2 different workbooks. The only output I need is:
if all cells in Column A from Workbook 1 match all cells in Column A from Workbook 2 (same order in both), then the nothing happens. If not, then the User gets an error message such as "Stop macro by pressing Esc".

Thanks in advance for all the help!

Cheers,
Tiago

 

[Fluff]

Please start a thread of your own for this. Thanks

 

[tmsousa]

Oh right! Will do! I just did the same in another thread, apologies...!!