Comparing 4 columns on Two different worksheets.

[Jimbob B]

I am trying to create a macro that will compare the data in Cells A
through D on two different sheets. Then post the results on a third
page.

The problem I am haveing is that there will be blanks on both reports
that can not be deleted. For instance on sheet 1 column A3 and A4 maybe
blank but they are assinged to the DATA in B3 and B4. But sheet 2 has
values for this.

I have tried the VLOOP function and that does not seem to be working the
way I need it too.

I have tried many different ways and seem to be getting no where. I
have been working on this project for about a year and I have scrapped
aleast 20 different concepts.

I am running out of paitence. Any Help would be greatly appreciated.

*** Sent via Developersdex http://www.developersdex.com ***

 

[JLGWhiz]

Your post is a little vague as to what you expect to see on sheet 3 but
maybe you can work with this and get what you need. Change sheet names to
suit your purposes.

Sub colCompare()
Dim lr1 As Long, lr2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Dim sh3 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
Set sh3 = Sheets("Sheet3")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr1
For j = 1 To 4
If sh1.Cells(i, j) <> "" And sh2.Cells(i, j) <> "" Then
If sh1.Cells(i, j).Value = sh2.Cells(i, j).Value Then

sh3.Cells(i, j) = "Match"
Else
sh3.Cells(i, j) = "No Match"
End If
End If
Next
Next
End Sub

 

[Jim Cone]

You can ignore or return blank cells using the IsEmpty function or
by determining the cell length (0 if blank) using the Len function.
Also, checking cells with the TypeName function ensures that you are
doing a comparison on the same type of data...
Range("C1") = ("frog" - 1234) doesn't produce much useful info.
--
Jim Cone
Portland, Oregon USA
(e-mail address removed) (remove xxx)
(trial version of XL Companion available upon request -
compares worksheets, selections, rows) - does other nice stuff too.



"Jimbob B" <[email protected]>
wrote in message
I am trying to create a macro that will compare the data in Cells A
through D on two different sheets. Then post the results on a third
page.
The problem I am having is that there will be blanks on both reports
that can not be deleted. For instance on sheet 1 column A3 and A4 maybe
blank but they are assinged to the DATA in B3 and B4. But sheet 2 has
values for this.
I have tried the VLOOP function and that does not seem to be working the
way I need it too.
I have tried many different ways and seem to be getting no where. I
have been working on this project for about a year and I have scrapped
aleast 20 different concepts.
I am running out of paitence. Any Help would be greatly appreciated.